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
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."
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.
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’.
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.
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.
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.
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.
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.
Step 2: Press Get Store App.
Step 3: Next, click the Open Microsoft Store button on the Pop-up Box.
Step 4: Press Get for software download.
Step 5: Downloading starts right away.
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.
How to Sort an Excel Spreadsheet by Date in WPS Spreadsheet?
Step 1: Highlight the data you want 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.
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.
Step 3: Choose Expand the selection and then click OK. This allows the cells to move based on cell position after sorting.
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.
