Pivot Table Issues

How to Fix Pivot Table Dates Sorted as 1, 10, 11 Instead of Sequentially

Steven ParkerSteven Parker Nov 3, 2026 872 views

Question details

The user needs to correct the sorting order of date headings in a Pivot Table, which are currently sorting alphabetically as text rather than chronologically.

How to Fix Pivot Table Dates Sorted as 1, 10, 11, 12 Instead of Sequentially
Product
Spreadsheet
Device & OS
not provided
Scenario
Organizing or refreshing data inside a Pivot Table where row or column headers represent dates or numbered months.
Observed behavior
Date headings are treated as text, causing them to sort in alphabetical order (e.g., 1, 10, 11, 12, 2, 3) instead of sequential numerical order (1, 2, 3, 4).
Before you start

Check your original data source to ensure there are no leading apostrophes or spaces in the date cells, which force the application to read them as text strings.

Solution 1Recommended

Convert Text Values to Genuine Date Formats

This is the most reliable method. By converting text strings back to real serial date values, the Pivot Table will automatically sort them sequentially.

Spreadsheet programs use sequential serial numbers to represent dates. When data is imported from external sources or CSV files, dates are often formatted as text.

Using the 'Text to Columns' feature is the quickest way to force the spreadsheet to recognize these text strings as true date values without manual re-entry.

1
Select the original date column

Navigate to the source data sheet and highlight the entire column containing the problematic dates.

2
Use Text to Columns

Go to the 'Data' tab on the top ribbon and click on 'Text to Columns'. Choose 'Delimited' and click Next twice to reach Step 3 of the wizard.

3
Specify the Date format

Under 'Column data format', select 'Date' and choose the format that matches your text (e.g., MDY or DMY). Click 'Finish' to apply the conversion.

4
Refresh the Pivot Table

Return to your Pivot Table, right-click anywhere inside it, and select 'Refresh'. The dates will now sort in standard chronological order.

Convert Text Values to Genuine Date Formats
Formatting Grouped Dates: Once the values are recognized as true dates, you can use the Pivot Table's built-in 'Group' feature to automatically organize them by Months, Quarters, or Years.
Effortless Data Analysis

Easily Manage and Sort Pivot Tables in WPS Office

WPS Spreadsheet provides intuitive data formatting tools and robust Pivot Table functionality to help you analyze your data effortlessly. Easily convert text to dates and apply custom sorting without complex workarounds.

  1. 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the file containing your source data and Pivot Table.
  2. 2. Select the problematic data: Highlight the column with the text-formatted dates or numbers.
  3. 3. Convert to Date: Right-click, select 'Format Cells', choose the 'Date' category, and apply a standard date format to instantly convert text to serial values.
  4. 4. Refresh Pivot Table: Navigate to the sheet with your Pivot Table, right-click the table, and select 'Refresh Data' to update the sorting order.
Highly compatible with Microsoft Excel file formats (.xlsx) and Pivot TablesBuilt-in smart formatting to easily convert text strings to genuine datesIntuitive drag-and-drop Pivot Table interface for quick data reorganizationFree and lightweight alternative for advanced spreadsheet operations
microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet treat numbers as text?

This usually happens when data is imported from external databases or CSV files. The application interprets the incoming data literally. Leading spaces, apostrophes, or mismatched locale settings can also force numbers and dates to be stored as text strings.

Can I manually rearrange items in a Pivot Table?

Yes. If you only have a few items to reorder, you can click on the cell containing the label (e.g., '10'), hover over the border of the cell until the cursor changes to a four-sided arrow, and drag it to the correct sequential position.

How do I sort a Pivot Table by a custom list?

You can define a custom list in your spreadsheet's advanced options (e.g., entering Jan, Feb, Mar sequentially). Once created, you can set the Pivot Table sorting options to 'More Sort Options', and choose your custom list to dictate the exact order regardless of alphabetical rules.

Does changing the format in the Pivot Table alter the original data?

No. Formatting changes applied directly within the Pivot Table only affect the display of the summarized data. However, to permanently fix text-to-date sorting issues, you must adjust the formatting of the original source data.