Why Excel PivotTable Slicers Don't Show Custom Sort & How to Fix It
Question details
The user is unable to find or use the Custom Sort option to arrange items inside an Excel PivotTable slicer.

- Product
- Excel 2019
- Device & OS
- not provided
- Scenario
- Attempting to sort PivotTable slicer items in a specific, non-alphabetical custom order.
- Observed behavior
- The Custom Sort option is unavailable or missing in the slicer settings, typically when the PivotTable is built from Power Query or the Data Model.
Verify your PivotTable's data source before troubleshooting, as slicers behave differently when connected to a standard Excel range compared to Power Query or external Data Models.
Create a Sort-Order Column in the Source Data
Since Power Query data models do not expose standard custom sort controls to slicers, adding a numeric helper column to your source data is the most reliable way to define a custom order.
Data loaded via Power Query or the internal Data Model bypasses standard Excel custom list sorting. By embedding the sort logic directly into the data source, the PivotTable and its slicers will respect the numeric sequence.
Navigate to the original data table or open the Power Query Editor where your data is being transformed.
Create a new column (e.g., named 'Sort Order') and assign numeric values (1, 2, 3...) that correspond to the exact sequence you want your slicer items to appear in.
If using Power BI or Power Pivot Data Model, select your main category column, click 'Sort by Column' on the ribbon, and choose your new 'Sort Order' column.
Close the editor, load the data, and click 'Refresh All' on the Data tab. Your slicer will now display the items in the sequence dictated by the sort-order column.

Use a Standard Excel Table as the Data Source
If your dataset is relatively simple and does not strictly require Power Query transformations, loading it directly as an Excel Table restores the native Custom Sort capabilities.
Create and Manage PivotTables Easily with WPS Office
WPS Spreadsheet provides a highly compatible, intuitive interface for managing PivotTables and slicers. You can easily insert slicers and organize your data without the friction of complex backend data model restrictions.
- 1. Open your data file: Launch WPS Spreadsheet and open the file containing your raw data.
- 2. Insert a PivotTable: Highlight your data range, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'.
- 3. Add a Slicer: Click anywhere inside your generated PivotTable, go to the PivotTable Tools Options tab, and click 'Insert Slicer'.
- 4. Adjust Slicer Settings: Right-click the inserted Slicer, choose 'Slicer Settings', and easily manage your item sorting and layout preferences.

Frequently Asked Questions
Why is the 'Use Custom Lists when sorting' checkbox greyed out in Slicer Settings?
This setting is automatically disabled or greyed out when your PivotTable is connected to an external data source, Power Query, or the internal Data Model (Power Pivot). It only works for standard worksheet data.
Can I sort an Excel slicer chronologically instead of alphabetically?
Yes. Ensure your source data formatting is strictly set to Date values. If your source data uses text for months (like 'January', 'February'), Excel will sort them alphabetically. You must define an Excel Custom List or add a numeric helper column (1-12) to force chronological sorting.
Will my custom slicer order work if I share the file with others?
If you rely on a local Excel Custom List, the sorting might break for other users unless they have the same list saved on their machine. Using a dedicated sort-order column in your source data guarantees the order remains identical for anyone opening the file.




