Sorting is a crucial part of organizing data. Being everyone's go-to spreadsheet tool, Excel thankfully offers various tools for sorting data like the sort feature and functions like SORT or SORTBY. However, neither of these approaches can really help you with sorting dates by month in Excel. For this, you need to do some additional work. In this article, we are going to show you how to sort dates by month in Excel.
We will show you two methods. The first method is using Excel’s Sort feature itself with the addition of a helper column. The other one is using Excel newest functions, SORTBY. Either one works fine, but remember that the SORTBY function is only available for Microsoft 365 (formerly Office 365) subscribers.
Sorting dates by month with a helper column
This is a generic approach which you can execute on any Excel version. Let’s say we have a list of people with their birthdays.
Obviously, Sort feature doesn’t have an option for month ordering specifically. Thus, we can use a helper column for the month values and sort by that column.
Month values can be retrieved with the MONTH function, which returns the month values from dates.
Next step is to use Excel’s Sort feature on the new helper column. Creating the helper column adjacent to source table will help you sort data easily.
- Select a cell in the helper column
- Click either the Sort Smallest to Largest or Sort Largest to Smallest icons to sort in the desired order
Here is the result after the table is sorted from small to large:
Alternatively, you can use the Sort dialog to sort based on more than one criteria. Detailed information about this feature can be found in our How to sort in Excel article.
Using SORTBY Function
SORTBY function is one of the recently introduced dynamic array functions. If you are a Microsoft 365 subscriber, you can sort dates by month with a formula. Of course, to use a formula you need to populate more columns. This approach is good if you don’t want to change your source data.
SORTBY function will simply sorts an array by given arrays in the corresponding sorting order. The trick is to use the MONTH function with date column as a sorting array.
What you need to do is pretty straightforward.
- (Optional) You may want to create headers for the target table which will populated by SORTBY
- Select the top left cell of the target table
- Enter the formula =SORTBY(<range of while table>, MONTH(<range of date column),<optional sorting order>)
Excel will populate cells automatically:
