How to Fix Pivot Table Dates Sorted as 1, 10, 11 Instead of Sequentially
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.

- 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).
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.
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.
Navigate to the source data sheet and highlight the entire column containing the problematic dates.
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.
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.
Return to your Pivot Table, right-click anywhere inside it, and select 'Refresh'. The dates will now sort in standard chronological order.

Pad Text Values with Leading Zeros
If you must keep the data formatted as text, standardizing the text length by adding leading zeros ensures alphabetical sorting aligns with numerical sorting.
Prefix Month Names with Numbers
For text-based month names (Jan, Feb, Mar) that sort alphabetically (Apr, Aug, Dec), adding a numerical prefix forces them into chronological order.
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. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the file containing your source data and Pivot Table.
- 2. Select the problematic data: Highlight the column with the text-formatted dates or numbers.
- 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. Refresh Pivot Table: Navigate to the sheet with your Pivot Table, right-click the table, and select 'Refresh Data' to update the sorting order.

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.




