Sorting helps you locate or highlight the desired data quickly. It is an excellent feature of Microsoft Excel for organizing and analyzing huge datasets. You can use filters, tools, and functions to sort data in Excel.
Almost every Excel spreadsheet contains a date column representing deadlines, birthdays, event schedules, etc.
This is a quick guide about how to sort by date in Excel. Let’s discuss the various tools and functions available within the program to sort and organize datasets by date. We will also uncover the most common mistakes while handling date data in Excel.
Here’s a list of various date-sorting methods discussed in this article. Click on the respective links to jump to that particular section:
Download the Example Microsoft Excel
Before we dive into the actual steps to sort by date, make sure you have an Excel spreadsheet with the required dataset to practice the methods mentioned above.
You can download the following Excel file, which we used to demonstrate various sorting methods in this article.
Note that practice will strengthen your skills in handling dates in Microsoft Excel and help you quickly organize large datasets in the future.
Now, without any further ado, let’s begin.
How To Sort By Date In Excel
Microsoft Excel offers the easiest ways to sort by date. Even beginners can quickly sort massive datasets as per their requirements using a few clicks.
But before using sorting tools or functions, make sure all of the dates entered in the given columns follow a specific date format accepted by Excel.
If somehow your dates follow an incorrect format, tools and functions will fail to display accurate results.
METHOD #1 – Sort by date in Excel using Filters
It might be the most common and easiest way to sort by date in Microsoft Excel.
Here are the steps,
- Open the desired Microsoft Excel
- Select any cell of the table containing the dates
- Hover to the main menu
- Click on the “Home” tab
- Click on the “Sort & Filter” option as shown in the following image,
- Select the “Filter” option from the popup
- A filter icon will appear in the header of the table, as shown below,
- Click on the “Filter” icon from the dates column
- Choose the “Sort Oldest to Newest” option
That’s all!
Excel will instantly sort the dates in ascending or chronological order. Refer to the above GIF.
Note that sorting will be applied to all the columns of the given table. In other words, Data corresponding to respective entries in the dates column will be adjusted automatically.
METHOD #2 – Sort by date in Excel using the SORT tool
This method is similar to the previous one, except it allows you to define multiple sorting criteria simultaneously.
It requires an equal amount of effort. You need to use the Excel main menu.
Here are the steps,
- Open the desired Microsoft Excel
- Click on any cell of the table
(Note that the tool automatically detects the entire table range. We suggest selecting the whole table instead of a single cell in case you face any error) - Hover to the main menu
- Click on the “Data” tab
- Choose the “Sort” tool from the list
- A new popup will appear on your screen
- Click on the dropdown below the “Column” option
(Here, you will see all the column headers of your table, as shown in the above image) - Select the column holding the dates that need to be sorted. In our case, the column “Hire Date” contains the dates
- For the second dropdown below the “Sort On” option, keep the selection to “Cell Values“
- Now, in the third dropdown, you can choose to sort the column in ascending or descending order
- Once done, click the “OK” button
Quick and easy!
You can apply sort to all of the columns, either in ascending or descending order.
METHOD #3 – Sort by date in Excel using the SORT function
Last but not least, this is the advanced sorting option in Microsoft Excel for pro users.
It creates a dynamic table that updates automatically whenever the source table data is changed. Simply put, instead of applying sort to the same table, the function allows you to create a new table where the entire data will be sorted according to the criteria defined.
Adding new records and also editing the existing ones breaks the sorting applied to the table. So, creating dynamic tables is helpful when the source data is subjected to frequent updates.
In short, dynamic tables are impressive!
Explained: The SORT Function in Excel
The SORT function is used for advanced data organization and analysis. The general syntax for the function is as follows,
=SORT(array, [sort_index], [sort_order], [by_col])Each argument of the above function needs to be replaced with the proper information,
- “array” – It represents the entire table range or array of values that needs to be sorted
- “sort_index” – Here, you can define the column number to be sorted from the array defined previously
- “sort_order” – This argument is used to define the sorting order. You can enter “1” to sort in ascending order and “-1” to sort in descending order
- “by_col” – This is an optional argument which is used to indicate the direction of sorting. You can enter “FALSE” to sort by row and “TRUE” to sort by column
The function is pretty straightforward.
It can be combined with other functions in Microsoft Excel for advanced data organization.
Now, let us use this function to sort by date.
Before we begin with the steps, make sure you add a new table, as shown in the following image.
Headers must be identical and follow the same sequence as the original or source table.
Here are the steps:
- Select the cell below the first header
- Type “=sort”
- Choose the first option from the popup or press the “Tab” key
- Replace the “array” argument with the entire table range
- Press “,” on your keyboard to move to the next argument
- Next, enter “2” in the place of the “sort_index” argument
(Dates are in second column, so we have entered the number 2) - Press “,” key
- Let us sort the dates in ascending order by putting “1” in the place of the “sort_order” argument
- Complete the bracket using “)“
- Press the “Enter” button on your keyboard
Here’s how the final formula should look,
=SORT(A3:C21,3,1)A new table will appear instantly. It is a dynamic table that auto-updates based on the changes in the source table.
This function is helpful when you need to preserve the old data and create a new table for further analysis. The old data will act as the raw data set for your new table, and you can treat it as a backup dataset.
What is the common mistake everybody makes while using the Sort function?
It is not keeping all the cells empty in the newly created table.
In other words, you need to ensure that all cells where the new sorted table will be placed as an output will be blank.
Look at the following example,
One of the cells contains “123,” as shown in the above image. After applying the Sort function, it will display the error “Spill range isn’t blank.”
You must ensure that the new table range is empty.
Also, in case of huge tables, if you see the “#SPILL” error, check the table range for the false entries.
How does the SORT function auto-update the sorted data table?
You might have seen that I was mentioning the term dynamic table repeatedly at the beginning of this section.
The SORT function creates a dynamic table to ensure auto-updates with respect to the changes made to the source data.
Refer to the following example table, where we have employee names and hire dates in two separate columns.
As you can see, a new table was created using the Sort function. We have applied to sort by dates in the Hire Date column.
Now, let us change David Owens’s hire date from 4/16/2023 to 4/16/2004. As soon as we do this, the new sorted table will automatically update, and the record for David Owens will be moved to the bottom.
How To Sort By Month In Excel
Consider a scenario where you wish to sort the birthdays of your colleagues by the month.
You need to do an extra workaround to sort by month in Excel, as there is no built-in tool or function.
Let us start by adding a helper column where we will obtain the number of each month using the MONTH function.
Here’s the formula,
=MONTH(serial_number)You need to replace the “serial_number” argument with the date. You can enter the date as is, but make sure to enclose it in double quotation marks. You can also provide the cell reference holding the date.
Let’s begin,
- Select the first cell of the helper column
- Type “=month”
- Choose the first option from the list or press the “Tab” key
- Replace the serial_number argument with the cell reference “B2“
(We have dates in the column B) - Close the parenthesis using “)“
- Press the “Enter” on your keyboard
Our final formula should look like the following,
=MONTH(B2)Once the numbers are obtained, we can sort them using the Filter tool.
Here’s how to do it,
- Select any cell of the table
- Hover to the main menu
- Click the “Home” tab
- Choose the “Sort & Filter” tool
- Next, click the “Filter” option from the list
- A filter icon will appear in the header row, as shown below,
- Click on the filter icon of the helper column
- Select the first option from the popup, which says, “Sort Oldest to Newest“
All of the dates are now sorted by the month.
You can easily view how many days there are in each month in the given table.
How To Sort By Year In Excel
Similar to the previous section, to sort by year, we will need a helper column.
We will use the YEAR function to obtain the year from the date.
=YEAR(serial_number)Where the “serial_number” argument needs to be replaced with the date, you can manually enter the date or provide the cell reference holding the same.
While manually entering the date, please make sure to use double quotation marks. Otherwise, the function will return an error.
Now, let’s begin,
- Select any cell of the table
- Go to the main menu and click the “Home” tab
- Next, choose the “Sort & Filter” option
- Click on the “Filter” option from the popup
- A filter icon will appear in the header row, as shown below,
- Click the filter icon of the helper column
- Select the first option named “Sort Oldest to Newest“
This way, all of the oldest dates will remain at the top, and new ones will move to the bottom.
How To Sort By Day Of The Week In Excel
Sorting by month or year seems obvious. But in some cases, you may need to sort the data by day of the week.
We need to use a built-in function to sort the data by the day of the week, similar to the previous sections.
Let’s start by adding a helper column to get the day of the week. Refer to the following image,
Now, we will use the WEEKDAY function, which has the following syntax,
=WEEKDAY(serial_number,[return_type])The “serial_number” argument needs to be replaced with the date. The “return_type” argument lets you decide the start of the week, such as Monday, Tuesday, Wednesday, and so on.
Here are the steps to get the day of the week,
- Select the first cell of the helper column
- Type “=weekday”
- Choose the first option from the popup or press the “Tab” key
- Next, for the serial_number argument, let us put the cell reference as “B2“
(The dates are in column B, and the first date is in cell B2) - Complete the bracket using “)“
- Press the “Enter” key on your keyboard
Once we receive the day of the week, it is time to sort the table.
Let’s begin,
- Select any cell of the table
- Go to the “Home” tab of the main menu
- Click on the “Sort & Filter” tool
- Choose the “Filter” option from the popup
- A filter icon will appear in the header row of the table
- Click the filter icon of the helper column
- Choose the first option, “Sort Smallest to Largest“
Excel will group all the dates by day of the week, as shown in the above GIF.
Further, you can also get the day of the week in words using the CHOOSE function.
=CHOOSE(WEEKDAY(C2), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")Make sure to replace the “D2” with the appropriate cell reference in the above formula.
Here’s how the table will look with the day of the week.
Q. What is the best way to sort birthdays in Excel by Month and Day?
It is one of the most sought-after questions when it comes to the topic of sorting by date in Microsoft Excel.
Consider a scenario where your job is to prepare a budget to celebrate students’ birthdays. Knowing the number of birthdays each month and day of the week is crucial in this case.
Here’s an example dataset where we have 20 students, along with their date of birth.
We will need a helper column in this case. Refer to the following image,
We will obtain the month and day from the given date in this column using the TEXT function. Let’s begin,
- Select the first cell of the helper column
- Type “=text”
- Choose the first option from the popup or press the “Tab” key
- Replace the value argument with the cell reference “B2“
- Next, type “,” to move to the next argument
- Replace the format_text argument with the “mm.dd”
(Make sure you put the double quotation marks, otherwise; the formula will return an error) - Close the bracket using “)“
- Hit the “Enter” button on your keyboard
Our final formula is as follows,
=TEXT(B2,”mm.dd”)Once we get the month and day in the helper column, we need to apply sorting.
Here are the steps,
- Select any cell of the table
- Hover to the main menu
- Click the “Home” tab
- Choose the “Sort & Filter” option
- Next, select the “Filter” option from the popup
- A filter icon will appear in the header row
- Click the filter icon of the helper column
- Choose the “Sort A to Z” option
That’s it!
All of the birth dates will be sorted in chronological order, as shown in the above GIF.
Conclusion
Excel boasts a massive number of tools and functions to organize and analyze datasets.
You can sort data by alphabet, numbers, text strings, dates, time, and much more.
If you are stuck somewhere while sorting the datasets by dates, then feel free to comment below.
Also, do not forget to explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.