How to Fix Excel PivotTable Custom List Sorting When Sharing Workbooks
Question details
The user needs to maintain specific PivotTable sorting structures, such as fiscal weeks or months, when sharing an Excel workbook with other users.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sharing an Excel workbook containing a PivotTable that relies on a locally defined custom list for sorting.
- Observed behavior
- The recipient sees the PivotTable sorted incorrectly (e.g., Week 10 appearing before Week 2) because the custom list settings are stored locally on the sender's computer and do not transfer with the shared workbook.
Verify that your raw data does not already contain a hidden helper column, and ensure you have edit access to the source data range connected to your PivotTable.
Format Text Values with Leading Zeros
Standardize your data format using leading zeros to force Excel's default alphabetical sorting to arrange items correctly without relying on custom lists.
Because custom lists are saved in the user's local application registry rather than the workbook itself, any sorting based on them will break on a different computer. Standard alphanumeric sorting evaluates character by character, which is why 'Week 10' is sorted before 'Week 2'. Adding a leading zero resolves this.
Navigate to the spreadsheet containing the raw data used for your PivotTable.
Change the single-digit week entries from 'Week 1', 'Week 2', etc., to 'Week 01', 'Week 02', up to 'Week 09'.
Return to your PivotTable, right-click anywhere inside it, and select 'Refresh'. The data will now automatically sort in the correct sequential order.

Create a Numeric Sort-Order Helper Column
Add a dedicated formula column to calculate a numeric value for complex date periods (like fiscal months) to use as a reliable sorting parameter.
Create and Share Sorted PivotTables Effortlessly in WPS Office
WPS Spreadsheet provides powerful data formatting, formula handling, and PivotTable capabilities, allowing you to easily set up helper columns and text formats that display consistently across all devices when shared.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Add a helper column: Insert a new column next to your raw data to act as the sorting key.
- 3. Apply standardization formulas: Use a formula like ="Week "&TEXT(A2,"00") to automatically generate consistent, alphabetically sortable text tags.
- 4. Insert PivotTable: Select the entire data range, click 'Insert' > 'PivotTable', and set up your rows.
- 5. Sort the results: Drag your newly created helper column into the PivotTable to guarantee the correct display order on any computer.

Frequently Asked Questions
Where are Excel custom lists saved?
Custom lists in Excel are stored locally in the application settings or Windows registry of the specific user's computer, rather than within the individual workbook itself.
Can I embed a custom list into an Excel file?
No, Excel does not provide a native feature to embed a custom list directly into a workbook for sharing. Modifying the source data format or adding a helper column is the recommended workaround.
Why does Week 10 appear before Week 2 in my PivotTable?
This happens due to alphanumeric sorting rules. The system evaluates text character by character from left to right. Since the character '1' in '10' comes before '2', Week 10 is sorted earlier. Using 'Week 01' and 'Week 02' fixes this behavior.
Does WPS Office support PivotTables and helper columns?
Yes, WPS Spreadsheet offers comprehensive support for inserting and managing PivotTables, as well as applying advanced formulas for helper columns, ensuring full compatibility with Excel files.




