How to Sort by Date in Excel
Excel’s built-in sort options are powerful and useful tools, but they do not correctly work always when sorting dates. In Excel, we must have the dates in one single format to sort the data by
date. You can also use the Excel sort options for re-arranging the whole table, not just a single column.
Sort Date in Ascending or Descending Order
The easiest way to sort data in Microsoft Excel by date is to sort it in chronological or reverse chronological order. This sorts the data with the latest date starting first, depending on your preference. To sorting the dates follows the following steps:
Step 1: Go to the Data menu tab and under the Sort & Filter category.
Step 2: Select the Sort option.
There is another way to apply the filter in the headers. Then from the drop-down of Date column, select Newest to Older or Vice-Versa to sort the date in excel.
Step 4: If you want to sort dates from Oldest to Newest, choose Oldest to Newest button.
Step 5: The Sort Warning dialog box will appear. Leave the default Expand the selection option selected, and click on the Sort button.
As a result, this will bring the old dates first and push the latest dates to the bottom.
Step 6: And if you want to sort dates from Newest to Oldest, then do this.
As a result, this will bring the latest dates to the top. And push the old dates to the bottom of the data.
How to Sort by Month in Excel
Suppose you want to sort dates by month, ignoring the year, such as grouping your colleagues or relatives’ grouping anniversary dates. In this case, the default Excel sort feature won’t work because it always considers the year, even if your cells are formatted to display only the month or month and day.
The solution is to add a helper column, extract the month number, and sort by that column. To get a month from the date, use the MONTH function.
In the below example, we extract the month number from the date in B2 by using the following formula:
NOTE: If the result is displayed as a date rather than a number, set the General format to the formula cells.
Step 1: And now, sort your table by the Month column.
Step 2: Select the month numbers (C2:C8), click Sort & Filter, choose Sort Smallest to Largest, and then expand the selection when Excel asks you to do so. If all done correctly, you will get the following result:
The data is now sorted by month, ignoring the years and days within each month.
How to Sort Birthdays by Month and Day
When arranging dates for a birthday calendar, the optimal solution will be sorting dates by month and day. So, it would help if you had a formula that would pull months and days from birth dates.
In this case, use the Excel TEXT functions, converting a date to a text string in the specified format. For our function, the “mmdd” or “mm.dd” format code will work.
In the below example, we extract the month and day from the date in B2 by using the following formula:
Next, sort the Month and day column from largest to smallest, and you will have the data arranged in order of the days of each month.
You can achieve the same result by using the DATE formula as given below:
This formula generates a list of dates by extracting the month and day from the actual date in B2 and replacing the entire year with a fake one, 2000 in this example.
The idea is to have the same year for all the dates and then sort the list of dates in chronological order. Since the year is the same, the dates will be sorted by month and day, which is exactly the same as the above result.
How to Sort by Days of the Week
To sort data by weekday, you will need a helper column. In this case, use the WEEKDAY formula that returns a number corresponding to the week’s day and then sorting by the helper column. A week that starts from Sunday (1) to Saturday (7) uses the following formula.
If your week starts from Monday (1) to Sunday (7), you can use the below formula:
In case the weekday names are entered as text, not as dates, then use the Custom Sort feature.
How to Sort Data by Month/Weekday Names
In case you have a list of month names as text, not in dates formatted to display only months. Applying Excel’s ascending sort might be a problem because it will arrange the month’s names alphabetically instead of sorting by month order from January to December. In this case, you need to use a custom sort feature and follow the following steps.
Step 1: Select the records that you want to sort by month name.
Step 2: Click on the Data tab, go to the Sort & Filter group, and click on the Sort button.
Step 3: In the Sort dialog box, do the following:
- First, choose to Continue with the current selection and then click the Sort
- Under column, select the name of the column that contains the month names.
- Under Sort On, choose Cell Values.
- Under Order, select Custom List.
Step 4: Again, it shows a Custom Lists dialog box in which you can select either full month names (January, February, March, …) or short names (Jan, Feb, Mar…) depending on how the months are listed in your worksheet.
Step 5: Click on the OK twice to close both dialog boxes.
As a result, the data has been sorted by month name in chronological order, not alphabetically.
Similarly, to sort by the week’s days’ names, select either full names (Sunday, Monday, Tuesday, …) or short names (Sun, Mon, Tue…) in the Custom Lists dialog box.
How to Auto Sort by Date
In excel, the Sort feature is not dynamic. It means you will have to re-sort your data with every change and whenever new information is added. There are two ways to accomplish this, such as macro and formula.
1. Auto Sort Dates with Macros
This macro is executed whenever a change occurs anywhere in the worksheet.
It is assumed that your data are in columns A through C, and the dates that you want to sort by are in column C. It is also assumed that row 1 contains headers. If your records are in different columns, then make the following adjustments:
- Change the A1 reference to the upper-left cell of your target range (including headers).
- Change the C2 reference to the topmost cell containing a date.
If you are working with a huge worksheet that contains a lot of information, re-sorting with absolutely any change in the sheet may be troublesome.
In this case, it makes sense to limit the trigger of the macro to the changes that occur in a specific range. The following VBA code sorts the data only when a change is made in column C that contains the dates.
NOTE: These macros can be used to auto-sort by any data type, not just dates. Our sample codes sort in ascending order. If you want to sort in descending order, then change Order1:=xlAscending to Order1:=xlDescending.
2. Auto Sort Dates with Formula
Supposing you have a list of dates and want to be arranged automatically in chronological order in a separate column, side-by-side with the original list. You can do this with the following array formula:
A2:A10 is the original (unsorted) dates, including a few empty cells for possible new entries.
Enter the formula in a blank cell beside the column with the original dates and press the Ctrl + Shift + Enter keys simultaneously to complete it. Then, drag the formula down to the remaining cells.
To have newly added dates sorted automatically, be sure to include a sufficient number of blank cells in the referred range.
Multiple Level Data Sorting
Multiple-level data sorting is used when you have a large data set. It means you can sort the dataset based on values in one column and then sort it again based on values in another column.
For example, suppose you have the dataset shown below and want to sort this data based on two criteria, such as country and sales.
In the above example, we have the data first sorted by the country, and then within each country, the data is further sorted by the sales value.
This quickly allows us to see which sales are doing great in each country or which ones are doing poorly. Below are the steps to sort the data based on multiple columns:
Step 1: Select the entire data set that you want to sort.
Step 2: Then, click on the Data tab.
Step 3: Click on the Sort Icon. This will open the Sort dialog box.
Step 4: In the Sort dialog box, make sure my data has headers is selected. In case your data doesn’t have headers, you can uncheck this.
Step 5: In the Sort Dialogue box, make the following selections:
- Under column, select the name of the column that contains the Country names.
- Under sort on, choose Cell Values.
- Under Order, select A to Z.
Step 6: Click on Add Level. This will add another level of sorting options.
Step 7: In the second level of sorting, make the following selections:
- Under column, select the name of the column that contains the Sales.
- Under sort on, choose Values.
- Under Order, select Largest to Smallest.
Step 8: Click on the OK button.
This sorting gives a dataset that sorted country into A to Z order and sales into largest to smallest order based on the country and sales criteria.
NOTE: The Sort dialog box has a feature to “Copy Level”. This quickly copies the selected sorting level, and then you can easily modify it. It’s a good-to-know feature and may end up saving your time in case you have to sort based on multiple columns.
