If you are looking to sort by date in Microsoft Excel, this blog post is for you. It will walk you through how to do it quickly and easily.
If you're trying to sort by date in Microsoft Excel, and wondering how to do it, you’re in the right place.
You’re not alone. Most Excel users rarely enter a date directly in Excel.
Excel sorts data based on the format you choose. So, to use the Date sorting feature properly, you'll need to understand how the DATE FUNCTION works in Microsoft Excel.
Whatever you type in excel, the app will convert into the proper format. If you use Excel's native date sorting functions, your Excel dates will be sorted in Excel's date order.
If your Excel data is not in an Excel standard column, it will default to the next best thing available.
So, let’s dive into sorting by dates in Excel.
Sort Excel by dates
To sort excel by date, do the following:
- Drag down the column to select the dates you want to sort.
- Click Home tab > arrow under Sort & Filter
- Click Sort Oldest to Newest, or Sort Newest to Oldest.
Let’s discuss this process in detail below!
Sort by Date in Excel (Windows and Mac)
Excel dates are easy to sort manually, but Excel’s automatic date sorting often fails. On Excel for Windows and Excel for Mac, you can use the native sort by date function to get the dates in a column sorted properly.
You can also use Excel’s Sort & Filter tool to further manipulate how your dates are displayed, and get more information at a glance. Some Excel versions have a feature that allows you to sort multiple columns by date as well. Alternatively, you can use an Excel macro similar to Multi Column Sort.
Note: This guide is written using Excel 2019, however, the steps apply to various other versions including Excel for Mac, Excel 2016, Excel 2013, and Excel for Microsoft 365.
-
Select the first date in your list, then drag down the column where your dates are to select all of them with the cursor. You may also hold the Shift key and click on the last date to select the entire column.
-
Go to the Home tab in your Ribbon, located on top of the Excel interface. Here, click on the Sort & Filter icon to expand its options.
- Choose either the Sort Oldest to Newest or Sort Newest to Oldest option depending on the desired result. You should immediately see your dates rearrange based on what you selected.
If you notice that your dates won’t change in order, or don’t have the option to sort them as dates, you might have them formatted as text. You can fix this by following the section below.
How To Convert Dates Stored As Text in Excel
Is the sort by date function not working in Excel? Typically, Excel will only sort cells that are converted into a date format. In Excel 2007 and newer, Excel sorts by the data type, you have selected.
If the date sorting feature doesn't work, there's a possibility that your cells are formatted as text. Excel will not sort cells that are formatted as text, so you'll need to convert them into Excel's date format.
-
Select your wrongly formatted dates, and then click on the drop-down menu in the Number section found in the Home tab. The menu should display “Text” or something other than the date.
-
Select either Short Date or Long Date from the drop-down menu. The formatting of your cells should automatically change, allowing you to sort by date.
Final Thoughts
Date sorting is one of the most underrated features in Excel, but it’s worth understanding how to use. When you sort by date, you get a different view of your data and see what needs attention quickly..