Pivot Table Issues

How to Fix Excel PivotTable Dates Sorting as Text

Chanuka GeekiyanageChanuka Geekiyanage Nov 10, 2026 868 views

Question details

Users experience an issue where dates inside a PivotTable sort alphabetically rather than from oldest to newest.

How to Fix Excel PivotTable Dates Sorting as Text Instead of Chronologically
Product
Excel
Device & OS
not provided
Scenario
Sorting a date field within a PivotTable to analyze chronological data.
Observed behavior
The date field sorts alphabetically because the source values, although looking like dates, are actually stored as text strings rather than numeric date serial values.
Before you start

Check your original source data column to see if the dates align to the left side of the cell, which is a strong indicator that Excel is treating them as text.

Solution 1Recommended

Convert Text to Dates Using Text to Columns

This is the most efficient method to convert an existing column of text dates into real Excel date serial values directly in the source data.

Simply changing the cell format to 'Date' from the ribbon will not change the underlying data type. You must force Excel to re-evaluate the text as actual numeric dates using the Text to Columns wizard.

1
Select the date column

Navigate to your original source data and highlight the entire column that contains the text dates.

2
Open Text to Columns

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

3
Navigate the wizard

In the Convert Text to Columns Wizard, keep 'Delimited' selected and click 'Next' twice until you reach Step 3.

4
Set the Date format

Select the 'Date' radio button under Column data format. Use the dropdown to choose the format that matches how your text dates currently look (e.g., MDY for Month-Day-Year), then click 'Finish'.

5
Refresh and sort the PivotTable

Go back to your PivotTable, right-click inside it, and select 'Refresh'. Click the filter/sort dropdown on the date field and choose 'Sort Oldest to Newest'.

Convert Text to Dates Using Text to Columns
Date Conversion Confirmed: Once properly converted, your dates will automatically align to the right side of their cells, confirming they are now recognized as numeric serial dates.

Easily Manage and Sort PivotTable Data with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools to instantly convert text strings into proper dates and build powerful PivotTables. Enjoy a lightweight interface with seamless data processing tools.

  1. 1. Open your dataset in WPS: Launch WPS Spreadsheet and open your workbook containing the text-formatted dates.
  2. 2. Convert text to dates: Select the date column, click the Data tab, and choose 'Text to Columns' to convert the text strings into valid date values.
  3. 3. Create or Refresh PivotTable: Go to the Insert tab and click 'PivotTable'. If you already have one, right-click it and choose 'Refresh'.
  4. 4. Sort Chronologically: Add the corrected Date field to the Rows area, click the dropdown arrow next to the row label, and select 'Sort Oldest to Newest'.
100% compatible with Microsoft Excel (.xlsx) files and PivotTablesBuilt-in Text to Columns wizard for fast, painless data cleanupAdvanced PivotTable controls to filter, group, and sort data chronologicallyLightweight and free to download without heavy system requirements
microsoft office alternative - wps office

Frequently Asked Questions

Why do my dates align to the left in Excel?

By default, Excel aligns text strings to the left and numeric values (which include real dates) to the right. If your dates are left-aligned without custom formatting applied, Excel is treating them as text.

Can I just change the cell format to 'Date' to fix the sorting?

No. Simply changing the format from the Home ribbon dropdown does not alter the underlying data type from text to a numeric serial value. You must use a tool like Text to Columns, Power Query, or a formula to force the conversion.

Why didn't my PivotTable update after I fixed the source data?

PivotTables cache their data and do not update automatically when source data changes. You must right-click anywhere inside the PivotTable and select 'Refresh' to pull in the corrected date values.

Can Power Query help fix text dates automatically?

Yes. If you import your data using Power Query, you can change the column's data type to 'Date' within the Power Query Editor. This ensures that the dates will always be recognized as chronological values when loaded into your PivotTable.