Catalog

How to Sort an Excel Spreadsheet by Date (4 Easy Methods)

November 15, 2023 4.1K views

We often want to sort budget transactions by date or track sales revenue by month in Excel, but we need to figure out how. How do we sort a spreadsheet by day, week, month, or year in Excel?  How do we put data in ascending or descending order? Follow along to learn how to sort data in Excel in this 2023 exclusive guide.

Method 1: Excel sort dates by Ascending or Descending Date (mm/dd/yyyy or dd/mm/yyyy)

Here's a brief discussion of each step:

Step 1: Ensure that you have a column (Column D) containing dates that you want to sort. The dates in this column should be in a consistent date format, such as "M/D/YYYY." or dd/mm/yyyy

Have a column with dates in a given order

Step 2: Click on any cell within the Column D that contains a date. Go to the "Home" tab on the Ribbon. It's usually one of the default tabs in Excel. Under the "Home" tab, locate and click "Sort & Filter."

Click on the column, then Home>Sort and filter> Sort Oldest to Newest

Step 3: Within the "Sort & Filter" menu, choose the option "Sort Oldest to Newest." Or “Sort Newest to Oldest’’ based on your preference. This option will instruct Excel to sort the selected columns in ascending or descending order.

Order has been set

Method 2: Excel Sort Date by Right-Clicking (The Simplest)

In this method, right-click any cell with a date, like in column D, and then go to the column and select Sort, then choose ‘Sort Oldest to Newest’ or ‘Sort Newest to Oldest’.

Click a column, select sort> then Sort Oldest to Newest or vice versa

The results will be the same as in the above method.

Method 3: Using Custom Sort to Sort Date in Excel

Custom sorting in Excel allows you to create a specific sorting order by defining your criteria. Here are some of the key features and advantages of custom sorting:

  • You can define a sorting order based on multiple criteria. That can include different columns and sorting directions (ascending or descending).

  • Custom sorting is highly flexible and allows you to sort data by more than just one column, making it suitable for complex datasets with various attributes.

  • You can define your sorting order, which can be helpful in situations where standard sorting methods don't apply.

Steps involved in using Custom Sort to sort by date in Excel include

Step 1: Start by selecting the dataset in Excel that you want to sort.

Step 2: Click on the "Home" tab in Excel to access the "Sort & Filter" toolbar.

Step 3: In the "Sort & Filter" toolbar, click "Custom Sort." That will open the "Sort" dialog box.

Select the dataset, then click Home> Sort &Filter> Custom Sort

Step 4: In the "Sort" dialog box, select "Delivery Date" from the "Sort by" box. Choose "Oldest to Newest" in the "Order" box.

Click Delivery Date, then select the second option as shown above, and finally, OK.

Step 5: Finally, click the "OK" button to apply the custom sorting criteria.

Method 4: Use an Excel Formula to Sort by Date in Excel with multiple columns.

Sorting data using Excel formulas offers several features and advantages. That includes;

  • Sorting by formula automates the sorting process. That makes it easier and more efficient to handle dynamic data.

  • Formulas can adapt to changes in the data. This ensures that your data remains sorted without manual intervention.

Below are the steps involved in using Excel Formula to sort by date.

Part One: Create a Helper Column to Calculate Relative Rank

Step 1: Open your Excel spreadsheet with the dynamic data you want to sort.

Dataset to be used

Step 2: Create a new column (e.g., Column G) and enter the following formula in the first cell (e.g., G2):

=COUNTIF(E$2:E$11,"<="&$e2)<>

Note that;

COUNTIF counts items in a specified range based on conditions.

E$2:E$11 is the range to be counted.

"<="&$e2>compares the value in the current row to the entire range, determining its rank.

Step 3: Copy this formula and paste it into each cell in Column G. This will generate a numerical ranking for each data entry, ranging from 1 to 10 in this example.

Copy this formula and paste it into each cell in Column G

Part Two: Reorder the Data Based on Rank Using INDEX, MATCH, and ROW

Step 1: Create a new column (e.g., Column H) where you want the sorted data to appear.

Step 2: Enter the following formula in the first cell (e.g., H2):

=INDEX($E$2:$E$11,MATCH(ROWS($G$2:G2),$G$2:$G$11,0))

Note that;

INDEX retrieves values from a specified range.

$E$2:$E$11 is the at to look in for the sales totals.

MATCH identifies the row to look in based on the value in the helper column.

ROWS($G$2:G2) counts down the rows, starting from the first cell in the series, which matches the relative rank.

Step 3: Copy this formula and paste it into each cell in Column H. That will display the sales figures in ascending order, maintaining the original rank information.

Paste the formula on column H

I have tried all the four methods discussed above over the last few years and found each way has distinct characteristics:

  • Method 1 (Standard Excel Sorting) is Quick and straightforward, Simple and user-friendly, and suitable for beginners. This method is Ideal for those who need a basic, quick sorting solution, especially when dates are in a consistent format.

  • Method 2 (Right-Click to Sort) is Quick and easy, straightforward, and perfect for users seeking a no-frills, quick sorting method. It's also beginner-friendly.

  • I found method 3 (Custom Sort) slightly more time-consuming due to the custom sorting setup, and it requires an understanding of custom sorting criteria. That makes it more suitable for intermediate users. It is recommended for users dealing with complex sorting requirements or multiple levels of sorting.

  • Method 4 (Sorting by Formula) requires more initial setup but automates sorting for dynamic data and is moderately complex. It is best suited for users comfortable with Excel formulas. Advanced users or those willing to invest more time in the setup will find this method influential for maintaining sorted data.

Best Free Alternative to Microsoft Excel - WPS Spreadsheet

WPS Spreadsheet is a versatile spreadsheet software that competes with popular office suites like Microsoft Excel, Google Sheets, LibreOffice Calc, and OpenOffice Calc. It offers seamless compatibility with standard file formats, including .xls, .xlsx, .xlsm, .xlt, and .csv, making it a flexible tool for working with spreadsheets.

I have found WPS Spreadsheet to be a user-friendly and feature-rich tool for spreadsheet tasks. Its interface closely resembles Microsoft Excel, making it easy for users familiar with Excel to transition. The software supports various functions, charts, and formatting options, making it suitable for a broad user base, from students to professionals.

Some of the pros of WPS Spreadsheet include;

  • WPS Spreadsheet is relatively lightweight compared to other office suites, ensuring smooth performance even on less powerful devices.

  • It offers the convenience of working on documents online, allowing for easy collaboration and access from different devices.

  • The software is known for its reliability and stability, minimising the risk of data loss or crashes during use.

Steps to Download WPS Office

Step 1: Access the official Microsoft Office Site on your browser.

Go to the Official WPS Office site

Step 2: Press Get Store App.

Press the " Get the Store App button

Step 3: Next, click the Open Microsoft Store button on the Pop-up Box.

Click on Open Microsoft Store

Step 4: Press Get for software download.

Click Get

Step 5: Downloading starts right away.

Software Downloading

Step 6: The Office tool has been downloaded. Press Open to access software and follow simple steps to access all the desired features on WPS Office.

Click open and follow other steps to access your desired features

How to Sort an Excel Spreadsheet by Date in WPS Spreadsheet?

Step 1: Highlight the data you want to sort.

Highlight the data to sort

Step 2: Go to the "Data" tab in the menu bar (ribbon), and under the "Data" tab, find and click on "Sort."

Step 3: Select "Ascending" or "Descending" based on your sorting preference.

Click Data>Sort>and selected Ascending & Descending

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

1.Why is Excel sort by date not working?

Trouble sorting by date in Excel may arise due to dates needing to be recognised or formatted as text. To resolve this, you can convert dates to the correct format using "Format Cells." If using the English UK date style, custom formats may be necessary.

2.How do I sort by date in Excel and keep rows together?

Step 1: In your Microsoft Excel, choose the column you wish to sort by date.

Step 2: In the Home menu, Click Sort, then Sort Largest to Smallest.

Select column> Home> Sort & Filter> Sort Largest to Smallest

Step 3: Choose Expand the selection and then click OK. This allows the cells to move based on cell position after sorting.

Click on Expand the selection and then OK


3.Why will my dates not be sorted in Excel?

In cases where Excel fails to recognize the column as date formats, you may not sort by dates in Excel.

Summary

The article discusses four methods to sort by date in Excel, highlighting their distinct characteristics and suitability for different users. Consider trying WPS Spreadsheet as a free alternative to Microsoft Excel. It is user-friendly and compatible with various file formats. It also has lightweight performance, online collaboration, and reliability.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.