Function Problems

How to Sort Excel Rows by Date Using a Formula (Excluding Blanks)

Camila Milosovich Oct 9, 2026 868 views

Question details

The user needs to sort an Excel data range by date while automatically filtering out any rows containing blank date fields.

How to Sort Excel Rows by Date Using a Formula

Product: Excel
Device & OS: not provided
Scenario: Organizing a chronological dataset and dynamically removing empty date entries without having to sort manually.
Observed behavior: The data should be sorted in ascending date order in a new range, omitting any rows where the date cell is blank.

Before you start

Ensure your date column contains valid Excel date values rather than text strings, and note the exact range of your dataset (e.g., A1:C30) before applying the formula.

Solution 1Recommended

Use SORT and FILTER Functions to Arrange Dates Dynamically

This method uses Excel's dynamic array functions to filter out blank dates and sort the remaining data in ascending chronological order automatically.

By combining the SORT and FILTER functions, you can extract a clean dataset from your original table. The FILTER function removes any rows with a date value of 0 (blank), and the SORT function orders the remaining rows based on the date column.

Identify your data range

Determine the full range of your data (e.g., A1:C30) and the specific column containing the dates (e.g., column A).

Select a destination cell

Click on an empty cell where you want the sorted data to appear, ensuring there is enough blank space below and to the right to accommodate the results.

Enter the combination formula

Type the formula =SORT(FILTER(A1:C30, A1:A30>0), 1, 1) into your chosen destination cell.

Execute the formula

Press Enter. The filtered and chronologically sorted data will automatically spill into the adjacent cells.

Use SORT and FILTER Functions to Arrange Dates Dynamically

Dynamic Updates: Because this is a dynamic array formula, any future dates added or modified in the original dataset (A1:C30) will automatically update in your sorted results.

Solution 2

Sort Manually Using the Custom Sort Feature

If you are using an older version of Excel that does not support dynamic array formulas, you can filter blanks and sort the data using built-in ribbon tools.

Sort Data Easily in WPS Office

Use WPS Spreadsheet to Sort and Manage Your Data Effectively

WPS Spreadsheet fully supports advanced dynamic array formulas like SORT and FILTER, allowing you to organize your date-based datasets effortlessly without altering the original data.

1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your dataset.
2. Input the SORT and FILTER formula: Select a blank cell with enough surrounding space and enter =SORT(FILTER(A1:C30, A1:A30>0), 1, 1).
3. Manage dynamic results: Press Enter to view the instantly sorted data. Updates made to the source table will immediately reflect in the formula's output.

Fully compatible with Microsoft Excel file formats (.xlsx) and formulasNative support for dynamic arrays to automate data sorting and filteringLightweight, fast, and completely free to useFamiliar tabbed interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why is the SORT function returning a #SPILL! error?

A #SPILL! error occurs when the destination range does not have enough empty cells to display the entirety of the sorted data. Clear any text or values in the cells directly below and to the right of your formula to fix this issue.

Can I sort dates in descending order instead of ascending?

Yes. In the formula =SORT(FILTER(A1:C30, A1:A30>0), 1, 1), change the last '1' to '-1'. The formula =SORT(FILTER(A1:C30, A1:A30>0), 1, -1) will sort the dates from newest to oldest.

What if my dates are formatted as text instead of values?

Formulas that check for values greater than zero (A1:A30>0) will not work properly if dates are stored as text. You can fix this by selecting your dates, navigating to 'Data' > 'Text to Columns', and clicking 'Finish' to convert them to valid serial date values.

Is the SORT function available in all versions of Excel?

No, the SORT and FILTER functions are dynamic array functions exclusively available in Microsoft 365, Excel 2021, and modern alternatives like WPS Spreadsheet. For older versions, you must rely on manual sorting or complex INDEX and MATCH arrays.

Excel & Data Problems