Others

How to Display Excel Date Filter Years from Oldest to Newest

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to change the order of year values in an Excel Date filter list so they display from oldest to newest.

Product: Excel
Device & OS: not provided
Scenario: Filtering an Excel worksheet containing date columns to find specific chronological data.
Observed behavior: The year values inside the Date filter drop-down list appear in descending order (newest to oldest), and sorting the actual worksheet data does not fix the filter list order.

Before you start

Before attempting to fix the filter, make sure your date column does not contain blank cells or mixed data types, as this can prevent Excel from recognizing the column as a true date field.

Solution 1Recommended

Convert Text Values to Real Excel Dates

Excel filters read text values differently than standard date values. Converting your column to proper dates will allow the filter to group and order the years correctly.

Often, data imported from other software or entered manually is formatted as text. When Excel sees text, it cannot apply its native date grouping logic.

By using the Text to Columns tool, you can force Excel to re-evaluate the text as actual date serial numbers, fixing the underlying filter issue.

Select the Data

Highlight the entire column containing the dates that are not filtering correctly.

Open Text to Columns

Navigate to the 'Data' tab on the ribbon and click on 'Text to Columns'.

Configure the Wizard

Keep clicking 'Next' until you reach Step 3 of the wizard. Under 'Column data format', select 'Date' and choose the format that matches your text (e.g., MDY or DMY), then click 'Finish'.

Reapply the Filter

Go back to the 'Data' tab, click the 'Filter' button to remove existing filters, and then click it again to reapply the filter to your headers.

Verify Date Format: To confirm the fix worked, temporarily format the column as 'Long Date' via the Home tab. If the text expands into a full date string with words, Excel has successfully recognized it as a real date.

Efficient Data Management

Easily Manage and Filter Dates with WPS Spreadsheet

WPS Spreadsheet intelligently recognizes date formats and provides robust filtering options, ensuring your chronological data is organized exactly how you need it without the hassle of manual conversions.

1. Open Your File: Launch WPS Office and open your spreadsheet document containing the date lists.
2. Select Date Cells: Highlight the specific column where your dates are located.
3. Format as Date: Right-click the selection, click 'Format Cells', choose 'Date' under the Category list, and select your preferred locale format.
4. Apply AutoFilter: Go to the 'Data' tab and click 'AutoFilter' to activate the drop-down arrows and sort your grouped years.

Intelligent date recognition and automatic formatting upon data entryFully compatible with Microsoft Excel (.xlsx, .xls) formats and structuresAdvanced drop-down filtering, grouping, and chronological sorting featuresLightweight and completely free for daily office and data analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why are my dates not sorting chronologically in the filter drop-down?

This usually occurs because Excel is treating the date entries as text strings rather than numeric date values. Because text is sorted alphabetically rather than chronologically, the filter list becomes disorganized. Converting the text to actual dates resolves the problem.

Does sorting the worksheet affect the filter drop-down order?

No. Sorting the data in your spreadsheet changes the visual order of the rows in the worksheet itself, but it does not change the default descending order (newest to oldest) of the grouped dates inside the filter menu.

How can I quickly check if an Excel date is stored as text?

Select the cell and look at its alignment. By default, Excel aligns text to the left side of the cell and numbers (including valid dates) to the right. You can also change the cell format to 'Number'; if it changes into a 5-digit serial number, it is a valid date.

Is there a way to force the Excel filter menu to show oldest to newest by default?

Excel's default grouping behavior for dates inside the filter drop-down is newest to oldest, and there is no built-in toggle to permanently invert this menu visually. However, to quickly locate older dates, you can use the 'Date Filters' option and select 'Before' or 'Between' instead of scrolling manually.

Excel & Data Problems