Do you have a large data set to analyze? Then, you may need to sort them in chronological order. And Excel can do this job easily with some of its default tools. But sometimes they are unable to do what we want. Go through this article to learn about how to sort by date in Excel.
Let’s learn how to use the default functions of Excel. Then, you have to move on to the usage of the combination of built-in functions and formulas.
The built-in sort options of Excel are effective and powerful tools. But regarding sorting dates, these may not work sometimes. In this article, you will get to learn some useful tricks that will help you to learn how to sort by date in excel without mixing data.
How To Sort Excel Columns By Ascending Or Descending Date:
You can easily sort an individual column in Excel into ascending or descending date order:
- Your first job is to select the data you are willing to sort.
- After that, your task is to choose Sort & Filter from the menu bar at the top of your screen under the Home tab.
- Next, you have to select Sort Oldest to Newest or Sort Newest to Oldest.
Alternatively, if you select your data and right-click to choose Sort, you will get the same result. Now, you have to select the order which is the most suitable for your data.
It will work when you want to sort a list of dates that will not need to correspond with other data. You are able to order your dates chronologically by sorting this way, especially by year first, then by month, and then by day.
Do you have tables of data or use Excel spreadsheet templates in order to organize your life? Then, you need to use other ways through which you can sort by date in Excel. Let’s learn how to sort by date in Excel table.
How To Sort Entire Tables By Ascending Or Descending Date In Excel:
If you want to sort dates in a table, you need to add headings in the top section of every column. For instance, when you create a basic Gantt chart in Excel, you will get to see a list of tasks corresponding to a list of dates. Here, we will discuss how you should use these headings as categories to sort your data.
Your first job is to make sure that you have selected your data correctly. Highlight all data while sorting in a table. Suppose you have highlighted one column. If so, then only one column will be highlighted. Other corresponding columns will remain in their original order. Therefore, you can see the dates do not match up with the correct data.
In order to go back to the original arrangement, you have to tap on Undo when you catch the error.
Otherwise, you can revert to your document’s old version or may need to re-enter the data. However, Excel is able to catch the error and prevent it in time.
You will receive a warning from Excel when you try to sort with the chosen dates only. Excel will ask you if you are willing to expand your selection. As soon as the error message pops up, your job is to choose Expand the selection. Then, you have to tap on Sort. Now, make sure that you do not forget to re-highlight the data’s entire table, not just one column.
After that, your job is to choose the headings & data that you are willing to sort. Now, you need to tap on Home > Sort & Filter. Next, your job is to choose your sort order.
You need to know that it works only when the data is in the first column of the table. Whether you are willing to sort by date once the dates are listed in a different column, you have to use Custom Sort.
Using Excel’s Custom Sort To Sort By Date:
It is one of the useful ways to sort by date in Excel. Whether your data has headings, you are capable of using them as categories in order to sort entries, though they are not in the first column.
A simple sort can help to sort your items alphabetically in the Transaction column rather than placing the dates in order. If the date is available in the second column, a Custom Sort needs to be used to tell Excel to sort the dates in Column B.
In order to do this, you need to highlight the headings along with the data below them.
- You need to tap on Home, then Sort & Filter, and then click on Custom Sort in the Excel ribbon.
- After that, in order to use your data headers as sorting categories, your job is to check the box in the top-right corner.
- Then, you have to select Date from the dropdown menu adjacent to Sort by.
- After heading toward the Order drop-down menu, you need to choose Oldest to Newest or Newest to Oldest.
Custom Sort allows you to select many sorting categories. For instance, the function can be used when you are willing to see the transactions listed first by one column and then by another.
You have to choose Sort by > [First Column] in the Custom Sort box. Next, your task is to tap on Add Level > Then by > [Second Column]. Now, your job is to sort your table by tapping on OK.
How To Sort By Date In Excel:
You can use the Excel sort options to rearrange the whole table, not one column. You have to expand the selection once prompted in order to sort records by date, keeping the rows intact.
These are the steps you need to follow to sort data in Excel by date:
- You first have to choose the dates without the column header in the spreadsheet.
- Then, your job is to tap on Sort & Filter on the Home tab. Now, you have to select Sort Oldest to Newest.
- Next, you will see the Sort Warning dialog box. After choosing the default Expand the selection option, you need to tap on Sort.
Thus, you can sort the records by date by keeping the rows intact.
How To Sort By Month In Excel:
You may want to sort dates by month several times, ignoring the year, mainly when you group the anniversary dates of any relative or colleague. Hence, you can see the default Excel sort feature not working as it considers the year despite your cells being formatted to display only the month or month and day.
You can fix the issue by adding a helper column, extracting the month number & sorting by that column. In order to get a month from the date, the MONTH function has to be used.
Suppose you extract the month number from the date in B2 using the following formula:
=MONTH(B2)
You need to sort data by month by extracting the month number.
When you see the result as a date instead of a number, you are able to set the General format to the formula cells.
- Now, your task is to sort the table by the Month column. In this case, you have to choose the month numbers (C2:C8).
- Then, you need to tap on Sort & Filter > Sort Smallest to Largest.
- After that, you have to expand the selection once Excel tells you to do it.
- Once you do everything properly, you will get the result.
You need to be aware that the data is sorted by month. It ignores years & days within every month. Whether you are willing to sort by month & day, you have to follow the instructions from the next example.
If you enter month names as text, you need to sort them by a custom list.
How To Sort Birthdays In Excel By Month And Day:
If you are willing to arrange dates for a birthday calendar, sorting dates by month & day is the best solution. Consequently, a formula is required to pull months & days from the birth dates.
Hence, you can use the Excel TEXT function, which allows you to convert a date to a text string in the specified format. Sometimes, you can see the “mmdd” or “mm.dd” format code works for you.
The formula takes the following form with the source data in B2:
=TEXT(B2, “mm.dd”)
After that, your job is to sort the Month & day columns from largest to smallest. Thus, you are able to arrange the data in order of the days of every month.
You can get the exact result while using the DATE formula:
=DATE(2000, MONTH(B2),DAY(B2))
Hence, a list of dates is created by the formula. It extracts the month & day from the actual date in B2. Besides, it replaces the real year with the fake year. For instance, we have taken the year 2000, but you can take any year. The idea is that the same year needs to be there for all the dates. After that, you have to sort the list of dates in chronological order. As you are having the same year, you can sort the dates by month and date. To learn how to sort by date in excel with different years, don’t forget to go through the entire article.
How To Sort Data By Year In Excel:
Regarding sorting by year, arranging dates in chronological order using the ascending sort (Oldest to Newest) option of Excel is the simplest way.
It helps to sort dates by year, month, and day. Whether you are not happy with this type of agreement, you are able to add a helper column with the YEAR formula used to extract the year from the date:
=YEAR(C2)
Once you sort data by the Year column, you are capable of viewing the dates, which are only sorted by year, not by months and days.
Disregarding months & years, if you are willing to sort dates by day, you need to use the DAY function to extract the day. After that, your job is to sort by the Day column:
=DAY(B2)
How To Sort By Days Of The Week In Excel:
When you want to sort data by weekday, remember that you will require a helper column. Hence, you have to populate the helper column using the WEEKDAY formula. It will return a number that corresponds to the day of the week. After that, you have to use the helper column to sort.
If a week begins from Sunday (1) and ends with Saturday (7), you have to use this formula:
=WEEKDAY(A2)
When a week begins from Monday (1) to Sunday (7), you have to use the following formula:
=WEEKDAY(A2, 2)
In this case, A2 represents the cell containing the date.
When you see the weekday names entered as text instead of dates, you are able to use the Custom Sort feature.
How To Sort Data In Excel By Month Names (Or Weekday Names):
If you have a list of month names as text instead of dates, which are formatted to display only months, you can face issues while applying the ascending sort of Excel. It helps to arrange months’ names alphabetically rather than sorting by month order from January to December. Hence, you can take the help of a custom Sort.
- You have to choose the records that you are willing to sort by month name.
- Then, you need to tap on Sort in the Sort & Filter group on the Data tab.
- Now, you have to do the following things in the Sort dialog box:
- Your task is to choose the name of the column where the month names are available under Column.
- Then, you need to select Cell Values under Sort.
- Next, you have to choose Custom List under Order.
- You can choose full month names (January, February, March, etc.) in the Custom Lists dialog box, or you can select short names (Jan, Feb, Mar…) based on how months are listed in the worksheet.
- Now, you have to tap on OK twice in order to close both dialog boxes.
- Finally, you can sort your data by month name in chronological order instead of alphabetically.
Whether you are willing to sort by the names of weekdays, you have to choose full names (Sunday, Monday, Tuesday, etc.) in the Custom Lists dialog box. Otherwise, you need to select short names (Sun, Mon, Tue…) in the Custom Lists dialog box.
How To Auto Sort By Date In Excel:
You might have noticed that the Excel Sort feature has faced several challenges. It’s not dynamic, which is its only disadvantage. It means that you need to resort to your data with each change and every time when new information is added. Are you wondering whether there is any way to sort data automatically to keep the data in order whenever a new date is added?
Then, you have to know that using a macro is the most effective way to accomplish it. You can see here some code examples that you need to use to auto-sort the following data by date in chronological order.
Macro 1— Auto Sort With Every Worksheet Change:
When a change occurs in the worksheet, the macro is executed.
Suppose your data is available in columns A through C. The dates you are willing to sort by exist in column C, starting in C2. Row 1 has headers (Header:=xlYes). Whether you see that the records are in different columns, you have to adjust a few things:
You need to change the A1 reference to your target range’s upper left cell (including headers).
You have to change the C2 reference to the topmost cell, which contains a date.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Range(“A1”).Sort Key1:=Range(“C2”), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
Macro 2— Auto Sort When Changes Are Made To A Specific Range:
Whether you need to work with a big worksheet with plenty of information, then you will face issues in resorting to any change in the sheet. Hence, you have to limit the macro’s triggering to the changes that can occur in a particular range. If you have made a change in column C containing the dates, this given VBA code helps to sort the data only.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(“C:C”)) Is Nothing Then
Range(“A1”).Sort Key1:=Range(“C2”), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub
You are able to use these macros to auto-sort by any data type, not only dates. Whether you are willing to sort descending, change Order1:=xlAscending to Order1:=xlDescending.
How To Add The Macro To Your Worksheet:
These macros are capable of running automatically on a worksheet’s change. Therefore, you need to insert the code in the sheet where you are willing to sort data. The process you need to follow is:
- You first have to open the VBA Editor by tapping on Alt + F11.
- Double-click the sheet where you are willing to sort automatically in the Project Explorer on the left.
- Finally, you need to paste the code in the Code window.
Auto Sort Dates With Formula:
Let’s assume that there is a list of dates that you want to be automatically arranged in a separate column chronologically, side-by-side with the original list. You can do this with the array formula:
=IFERROR(INDEX($A$2:$A$20, MATCH(ROWS($A$2:A2), COUNTIF($A$2:$A$20, “<=”&$A$2:$A$20), 0)), “”)
Hence, A2:A20 represents the unsorted or real dates that include some void cells for possible new entries.
Once you see a blank cell near the column, you need to put the formula with the original dates ( hence, C2 is shown). Then, you have to complete this by pressing the Ctrl + Shift + Enter keys simultaneously. After that, your task is to drag the formula down to the leftover cells.
If you want to sort the dates, which are newly added, ensure that you add enough blank cells in the preferred range. In this example, the list of dates is available in the range A2:A7. However, after supplying $A$2:$A$20 to the formula, you have to populate this into the cells C2 through C20. You can use the IFERROR function to prevent errors in the additional cells that return a void string (“”) instead.
What If Sort by Date Is Not Working In Excel?
Are you wondering how to sort by date in excel doesn’t work? If Excel still doesn’t work after applying all the processes mentioned above, check that you have entered this in a format that Excel will recognize. Whether all numbers are left-justified in their cells, it could happen that you have stored them as text. In order to solve the format, your task is to tap on the Text dropdown menu in the Excel ribbon. Finally, you should choose a date format.
The Bottom Line:
Although sorting dates looks like a simple task, it is something that you need to do accurately. So, you have to leave it to the sorting feature of Excel in order to sort your dates effectively. Excel has a great capability of sorting by date and comes with several built-in applications. In addition, a lot of useful spreadsheet templates exist that help to complete every task, like wedding planning, budgeting, and expense tracking. You can realize now how simple this is to sort by date in Excel. Still, if you have any queries or confusions, feel free to ask us via comments.
Frequently Asked Questions
Why can’t you sort by date in Excel?
Sometimes, the date sorting feature may not work properly, and the reason may be your cells are formatted as text. Remember that Excel doesn’t sort cells that are formatted as text. Therefore, you have to convert the cells into the data format of Excel.
What is the shortcut to sort by date in Excel?
The steps you have to follow are:
Your first task is to head toward the data tab. Then, you need to open the sorting option by tapping on the sort button. The keyboard shortcut A ➤ S ➤ S can also be used.
Next, you have to choose the column which you are willing to sort in the SORT option. Then, you have to choose the order you want to follow to sort.
How do you filter date-wise in Excel?
These are the steps you need to follow to learn how to sort by date in excel pivot table:-
- You first have to move to a PivotTable or PivotChart in the same workbook.
- After that, your job is to add a column to the Power Pivot field list’s Column Labels or Row Labels area from the Date table.
- Then, you need to tap on the down arrow adjacent to Column Labels or Row Labels in the PivotTable.
- Finally, you have to point to Date Filters. Next, you should choose a filter from the list.
