How to Sort Rows by Date in Excel (8 Methods)

It’s common to have data with dates and users usually sort rows in Excel by date. Various Excel features such as Sort & Filter. Filter, Custom Sort, Context Menu Options, and multiple functions such as MONTH, YEAR, SORT, SORTBY, etc. as well as VBA Macro effectively sort rows in Excel by Date.

Let’s say we have a dataset that stores the Total Cost incurred on a particular date. Now, we want to sort the entire dataset by date.

In this article, we demonstrate multiple features, functions, and VBA Macro to sort rows by date.

Download Excel Workbook

8 Easy Ways to Sort Rows by Date in Excel

Sorting is an essential tool to comprehend a dataset. Sorting by date helps users to get around with their data. There are multiple ways to sort data by date in place or different places in the datasheet. Follow the below section to effectively sort data by date using any of the described methods.

Method 1: Using Sort & Filter Feature

Excel offers Sort & Filter features in both the Home and Data tab. Use any of them to sort rows by date.

🔺 To apply Sort & Filter, go to Home > Choose Sort & Filter (from the Editing section) > Select Sort Oldest to Newest (A⟶Z). You can use any of the sorting options (i.e., Sort Oldest to Newest (A⟶Z) or Sort Newest to Oldest (Z⟶A)).

🔼 Alternatively, you can hover to Data > Click on (A⟶Z) or (Z⟶A) from the Sort & Filter section.

🔺 In both cases, Excel displays the Sort Warning whether you want to expand the sorting or not. Sorting Continuing with current selection disqualifies the fundamental purpose of sorting. As a result, Choose Expand the selection and Click Sort.

🔼 At the end of both options, you encounter a similar outcome as shown in the image below. From the depiction, you can see the entire dataset is sorted by date (Oldest to Newest).

Keyboard Shortcuts ALT+A+S+A executes the Oldest to Newest sorting and ALT+A+S+D does the Newest to Oldest sorting.

Read More: [Fix:] Sort and Filter Not Working in Excel

Method 2: Using Context Menu Options to Sort Rows by Date

🔺 Select the Date column within the dataset and right-click on it. The Context Menu appears. From the Context Menu, Select Sort > Select Sort Oldest to Newest or Sort Newest to Oldest.

🔺 The Sort Warning window pops up. From the window, Choose Expand the selection then Click on Sort. Alternatively, Selecting Continue with current selection results in sorting only the Date column hence making a blunder of the purpose of sorting.

🔼 After that Excel piles the data according to the selection (Oldest to Newest or Newest to Oldest) similar to the following picture.

Read More: How to Sort Data by Row Not Column in Excel (2 Easy Methods)

Method 3: Using Filter Feature to Sort Rows

Similar to the Sort & Filter feature, Filter can sort data by date. Let’s start with the method.

🔺 First, move to Home > Choose Sort & Filter (from the Editing section) > Select Filter to apply it to the dataset.

🔺 Afterward, Click on the Filter icon beside the Date column header. The Filter command box appears. Choose any of the options (i.e., Sort Oldest to Newest or Sort Newest to Oldest) to sort rows by date.

🔼 Now, you can check whether your dataset gets sorted by your desired selection or not.

Read More: Difference Between Sort and Filter in Excel

Method 4: Sorts Rows by Date Using Custom Sort

Custom Sort option offers sorting using any column header depending on Cell’s Value, Color, Font Color, and Conditional Formatting Icon. For Date sorting it offers Oldest to Newest, Newest to Oldest, and Custom List.

🔺Go to the Home tab > Click on Sort & Filter (from the Editing section) > Select Custom Sort.

🔺 The Sort dialog box opens up.

⧫ Choose Order Date as Sort by.

⧫ Cell Values as Sort On.

⧫ Oldest to Newest or Newest to Oldest as Order.

At last, Click on OK.

🔼 In a moment, you see the entire dataset get sorted according to your selection as depicted below.

Read More: How to Create Custom Sort List in Excel

Similar Readings

• Auto Sort When Data Is Entered in Excel (3 Methods)
• How to Sort by Last Name in Excel (4 Methods)
• Sorting Columns in Excel While Keeping Rows Together
• Sort Column by Value in Excel (5 Methods)
• Random Sort in Excel (Formulas + VBA)

Method 5: Sort by Date Using Function Outcomes

You can use a helper column to bring the month and year numbers from the dates using MONTH and DAY functions. Then, sort the entire dataset using the outcomes of those functions. We alter the dataset to use the MONTH function as a way to sort by date. The YEAR function can also be used depending on the data type.

🔺 Type the following formula in any blank cell (i.e., E5).

The formula fetches the month number from date entries.

🔺 Apply the Fill Handle to apply the formula to other cells.

🔺 Apply the Filter on the dataset using Method 3. Then, click on the Filter icon to sort the entire dataset by your desired options (i.e., Sort Smallest to Largest or Sort Largest to Smallest).

🔼 You see Excel sorts the entire dataset according to your selection as picturized in the following image.

Read More: Excel Sort By Date And Time [4 Smart Ways]

Method 6: Using SORT Family Function to Sort Rows by Date

Excel 365 provides two similar functions to execute sorting. The SORT and SORTBY functions sort the dataset using a particular column. The syntax of the SORT function is

The arguments of the function are

array; the range or array you are about to sort.

sort_index; column number that is being used for sorting. By default, it is 1. [Optional]

sort_order; types of sort order: Ascending = 1, Descending = -1. By default, the sort_order is Ascending. [Optional]

by_col; options such as Sort by Column = TRUE, Sort by Row = FALSE. By default, it is Sort by Row (i.e., FALSE). [Optional]

🔺 Use the following formula in any cell (i.e., H5).

In the formula,

array = B5:F16

sort_index = 1

sort_order = 1 (Ascending)

by_col = FALSE

🔺 Press ENTER to execute the formula. In a moment, the entire dataset gets sorted in a new place within the worksheet. As you choose the Ascending order as sort order, Excel arranges the entries in a similar way depending on the dates.

🔄 Unlike the SORT function, the SORTBY function offers multi-level sorting. The syntax of the SORTBY function is

The statements define

array; the range or array you are about to sort.

by_array; the range or array to sort by.

sort_order; the sort_order refers to Ascending = 1 (By default), Descending = -1.

array/order; Additional array and sort order pairs. [Optional]

🔺 Paste the below formula in any blank cells (i.e., H5) and press ENTER.

Comapring the syntax, array = B5:F16, by_array = B5:B16, sort_order = 1 (Ascending).

Read More: How to Sort Multiple Rows in Excel (2 Ways)

Method 7: Executing Auto Sort by Date Using Formula

The INDEX-MATCH formula allows dates to auto-sort by date. Type the following formula in any cell C5.

🔼 The MATCH(ROWS($B$5:B5),COUNTIF($B$5:$B$16, “<=”&$B$5:$B$16), 0) ➤ portion works as row_num for the INDEX function.

🔼 ROWS($B$5:B5) ➤ passes the lookup_value for the MATCH function using the ROWS function.

🔼 COUNTIF($B$5:$B$16, “<=”&$B$5:$B$16) ➤ passes the lookup_array for the MATCH function using the COUNTIF function.

🔼 0 ➤ indicates the match_type (i.e., Exact Match) for the MATCH function.

🔺 Hit ENTER and use the Fill Handle to apply the formula to other cells.

🔺 Alternatively, you can use the below formula to rank dates instead of the COUNTIF function and fetch particular columns.

The formula declares the same arguments as the previous formula instead of the COUNTIF function the RANK function is used. Also, the formula fetches specific columns according to your desire.

Read More: How to Auto Sort Table in Excel (5 Methods)

Method 8: Sort by Date Using VBA Macro

A VBA Macro can sort the dataset by date.

🔺 Use ALT+F11 to open the Microsoft Visual Basic. Double-click on the desired worksheet.

🔺 Paste the following macro in the Sheet’s code window.

Private Sub Worksheet_Change(ByVal Rng As Range)
On Error Resume Next
Range("B4").Sort Key1:=Range("B5"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

The first Range (i.e., B4) is the leftmost cell within the dataset. The latter Range (i.e., B5) defines the data starts. Then, the entire line of the macro commands the dataset to sort in Ascending order with Top to Bottom orientation.

🔼 After inserting the macro, return to the worksheet. Add or remove rows or simply hit ENTER to organize the data as shown in the below picture.

Read More: VBA to Sort Table in Excel (4 Methods)

Conclusion

In this article, we use multiple features, functions as well as VBA macro to sort rows in Excel by date. Inbuilt features offer multiple options to choose from. Also, customized formulas sort rows by date efficiently. VBA Macro auto sorts dataset by date. Hope these above-mentioned methods excel in their purpose for your case. Comment, if you have further inquiries or have anything to add.

Related Articles

• How to Use Sort Function in Excel VBA (8 Suitable Examples)
• How to Sort Array with Excel VBA (Both Ascending and Descending Order)
• VBA to Sort Column in Excel (4 Methods)
• How to Sort Unique List in Excel (10 Useful Methods)
• How to Add Sort Button in Excel (7 Methods)
• Sort IP Address in Excel (6 Methods)