Pivot Table Issues

Why Excel PivotTable Slicers Don't Show Custom Sort & How to Fix It

Steven ParkerSteven Parker Oct 23, 2026 871 views

Question details

The user is unable to find or use the Custom Sort option to arrange items inside an Excel PivotTable slicer.

Why Excel PivotTable Slicers Don't Show Custom Sort & How to Fix It
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.
Before you start

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.

Solution 1Recommended

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.

1
Open your source data

Navigate to the original data table or open the Power Query Editor where your data is being transformed.

2
Add a sort-order column

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.

3
Sort the target column

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.

4
Refresh the PivotTable

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.

Create a Sort-Order Column in the Source Data
Permanent Sorting: Using a sort-order column ensures that your custom sort remains intact even if the workbook is shared with other users on different computers.
Efficient Spreadsheet Management

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. 1. Open your data file: Launch WPS Spreadsheet and open the file containing your raw data.
  2. 2. Insert a PivotTable: Highlight your data range, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'.
  3. 3. Add a Slicer: Click anywhere inside your generated PivotTable, go to the PivotTable Tools Options tab, and click 'Insert Slicer'.
  4. 4. Adjust Slicer Settings: Right-click the inserted Slicer, choose 'Slicer Settings', and easily manage your item sorting and layout preferences.
Fully compatible with Microsoft Excel formats (.xlsx, .csv)Intuitive and easy-to-use PivotTable and Slicer insertionFree and lightweight office suite for everyday tasksFamiliar user interface requiring zero learning curve
microsoft office alternative - wps office

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.