How to Sort PivotTable Values as Numbers Instead of Text in WPS Spreadsheets
Question details
User needs to make a PivotTable sort values numerically instead of alphabetically by first digit because the source values are stored as text.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- User is working with a WPS Spreadsheets PivotTable and notices values such as 2000, 1000, 500, and 250 are sorted by their first digit rather than by numeric value.
- Observed behavior
- The PivotTable sorts values like 2000, 1000, 500, and 250 as text, producing an incorrect order such as 1000, 2000, 250, 500, because the source column contains text-formatted numbers.
Check the source data column used in the PivotTable: if the values are left-aligned, show a green error indicator, or contain apostrophes in the formula bar, they are likely stored as text. Identify the exact sheet and column before converting the data.
Convert the Source Column to Numbers Before Refreshing the PivotTable
Use this method when the PivotTable sorts numbers by their first digit and the source values are stored as text.
WPS Spreadsheets PivotTables sort according to the underlying data type of the source column. If the column is text, numeric sorting will not work even if the cells look like numbers.
Open the worksheet that feeds the PivotTable and select the entire column containing the values, such as the column with 2000, 1000, 500, and 250.
Click the warning icon if it appears and choose Convert to Number, or right-click the selection, click Format Cells, and set the category to Number.
Press Ctrl+H to open Find and Replace, then remove leading apostrophes, spaces, or non-breaking characters from the selected cells.
Click any cell inside the PivotTable, go to the PivotTable Analyze tab, click Refresh, and then sort the value field again.
Use Text to Columns to Convert a Large Text-Number Column
This bulk conversion method is useful when many cells are stored as text and the Convert to Number option does not appear.
Fix PivotTable Number Sorting in WPS Spreadsheets
You can correct PivotTable value sorting directly in WPS Spreadsheets by converting the source column to real numbers and refreshing the report.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel-compatible .xlsx or .xls file that contains the source data and PivotTable.
- 2. Convert text values to real numbers: Select the source column, click the warning icon and choose Convert to Number, or use Format Cells to set the Number format and remove apostrophes.
- 3. Refresh the PivotTable: Click the PivotTable, go to the PivotTable Analyze tab, and click Refresh.
- 4. Check the sort order: Click the value field drop-down, choose More Sort Options, and confirm the ascending or descending numeric order.
Frequently Asked Questions
Why does my PivotTable sort 1000 before 250?
This happens when the source column values are stored as text. WPS Spreadsheets then sorts character by character, so 1000 comes before 250 because 1 is smaller than 2. Convert the source values to numbers and refresh the PivotTable.
How can I tell if PivotTable source values are stored as text?
Select the source cells and look for left alignment, a green error indicator, or an apostrophe visible in the formula bar. You can also use the ISTEXT function in an empty cell to test a value.
How do I convert an entire column from text to numbers in WPS Spreadsheets?
Select the column, click the warning icon and choose Convert to Number, or go to Data > Text to Columns and click Finish. You can also use Paste Special with Multiply by 1.
Will changing the PivotTable number format fix the sort order?
No. Number formatting only changes how a value is displayed. Sorting depends on the underlying data type, so you need to convert the source values to real numbers.
Do I need to recreate the PivotTable after converting the source data?
Usually no. After converting the source column to numbers, click inside the PivotTable and use the Refresh command. If the order is still wrong, check for hidden text characters and refresh again.
