Pivot Table Issues

How to Fix Excel PivotTable Custom List Sorting When Sharing Workbooks

John WilsonJohn Wilson Oct 22, 2026 871 views

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.

How to Fix Excel PivotTable Custom List Sorting When Sharing Workbooks
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.
Before you start

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.

Solution 1Recommended

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.

1
Locate the source data

Navigate to the spreadsheet containing the raw data used for your PivotTable.

2
Modify the week numbers

Change the single-digit week entries from 'Week 1', 'Week 2', etc., to 'Week 01', 'Week 02', up to 'Week 09'.

3
Refresh the PivotTable

Return to your PivotTable, right-click anywhere inside it, and select 'Refresh'. The data will now automatically sort in the correct sequential order.

Format Text Values with Leading Zeros
Reliable Sharing: This method embeds the logic directly into the data, meaning the sort order will remain perfectly intact regardless of who opens the file.
Seamless Data Analysis

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
  2. 2. Add a helper column: Insert a new column next to your raw data to act as the sorting key.
  3. 3. Apply standardization formulas: Use a formula like ="Week "&TEXT(A2,"00") to automatically generate consistent, alphabetically sortable text tags.
  4. 4. Insert PivotTable: Select the entire data range, click 'Insert' > 'PivotTable', and set up your rows.
  5. 5. Sort the results: Drag your newly created helper column into the PivotTable to guarantee the correct display order on any computer.
Fully compatible with Microsoft Excel formats (.xlsx)Advanced PivotTable tools for robust data analysisLightweight application with a familiar, user-friendly interfaceSeamless workbook sharing without losing data structures
microsoft office alternative - wps office

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.