How to Sort by Date in Excel in 2 Minutes or Less (2023)
A date column is usually found in almost all Excel data😊
That’s why sorting data by date in Excel is quite popular.
Knowing how to sort data by date will help you complete your work in Excel easier and faster.
In this lesson, you’ll learn exactly how to sort data by date in Excel😀
Download this attached Excel workbook for you to practice so we can start.
Table of Contents
Sort with Auto-filter
It’s common practice to include auto-filter buttons when creating tables.
These automatic filter buttons let you sort dates in either ascending or descending order.
You’ve provided a list of a company’s employees’ birthdays in the table below.
Now, sort the above table by employee age. In other words, you have to arrange all of the rows based on the dates in the last column 🎂
You can do it by following the steps below.
- Expand the drop-down menu in the date of birth column.
If there are no filter buttons, you have to select any cell in your data range and press CTRL + Shift + L.
- If you want to sort all the dates in ascending order, select “Sort Oldest to Newest”. If you want to sort dates in descending order, select “Sort Newest to Oldest”.
If you select “Sort Oldest to Newest”, Excel will sort birthdays as follows.
If you select “Sort Newest to Oldest”, Excel will sort birthdays as follows.
Pro Tip:
Assume that you want to make a birthday calendar for the above list🎉
Then, you have to ignore years and consider only the dates and months to sort dates.
First, you have to use a helper column and create each source date with the same year.
You can use the below DATE formula for that.
You can use any year you like. In this case, it is 2023.
You can use the MONTH function and the DAY function to get the month and the date.
=DATE(2023,MONTH(C2),DAY(C2))
Next, you have to sort records using the newly created separate column (Column D).
Using the format code dd-mmm, you can change the date format to hide the year of each new date🤩
Sort with sort button
In some cases, we do not use auto-filter buttons in our data tables. In that situation, we are unable to sort dates using the auto filter option.
To sort data, Excel provides built-in tools. In other words, you can use the Excel sort button for that.
The following data set contains a list of employee birthdays.
Key point: You are not permitted to add filter keys to the data collection.
Now, you need to sort data by date of birth.
You can follow the below steps for that.
- Select any cell in your data list and go to the Data tab in the Excel ribbon.
- Click the “Sort” button in the Sort & Filter group.
- Choose the column with the date format as the “Sort by” option in the Sort dialog box. The “Sort On” dropdown menu should be set to “Cell values.” From the last drop-down option, choose the sort order. After you’re done, press “OK.”
You will get the same output as the previous sorting dates method.
You can notice that the first date of birth in the list is the oldest person on the list.
You can find the Sort dialog box in the Home tab also🏠
Expand the Sort & Filter dropdown menu in the Home tab and select “Custom Sort…”.
Then, complete the Sort Dialog box as earlier.
Sort with the SORT function
Sorting data is not dynamic in any of the previous examples. For example, if you alter a date after sorting, Excel will not auto-sort based on the new date😟
To sort dates dynamically, you can use the Excel SORT function, which is a built-in function 😎
SORT is one of the array formula built-in functions in Microsoft Excel.
In the example below, you have some invoice payment information. You have to now get data arranged according to the actual date of payment.
You can follow the below steps for that.
- Enter an equal sign and select the SORT function in cell E3.
=SORT(
- Select the array as the first argument of the SORT function. In this example, you have to select Cells A3 to C8.
Then, your updated formula is:
=SORT(A3:C8
- Give the sort index or the column index to sort the data range. In this example, actual payment is in the 3rd column of the selected data range. So, you can enter 3 as the sort index.
=SORT(A3:C8,3
- Select the sort order as the third argument of the function. Select 1 for ascending and select -1 for descending order. In this example, you can enter 1 for the third argument to sort dates in ascending order.
=SORT(A3:C8,3,1
- Close the parentheses and press the Enter key.
Your updated SORT formula is;
=SORT(A3:C8,3,1)
Even if you change any data subsequently, it will update and auto-sort your dates.
That’s it – Now what?
Well done! 👏
Learning how to sort by date in Excel is so helpful for you to work easier and faster.
But there are still a lot of incredible tools, features, and functions included with Excel. You only need to discover and learn them to your advantage.
My best recommendations? Get started with the most useful Excel functions like IF and SUMIF, and VLOOKUP.
Start learning them right here, right now💪
Enroll in my 30-minute free online course here that will teach you all these Excel functions (and many more) in the simplest and fastest way. Just like you want😊
Other relevant resources
Do you like to explore more about Excel SORT function? You can read our Excel SORT function article to learn the full scope of this magical function💡
You can also refer to our How to Sort in Excel article to explore more about Excel sorting data.
Now, do you want to unsort data? In Excel, you can do that too. Click here to see how to unsort in Excel.