To custom-sort an Excel slicer, create or use an Excel custom list for the item labels, then open the slicer’s settings and enable Use Custom Lists when sorting. Right-click the slicer, choose Slicer Settings, select ascending sort, and confirm; for a sequence that does not fit a custom list, use a sort key or a Data Model sort-by-column arrangement instead.
How slicer sorting works
A slicer is a clickable filter for a PivotTable, PivotChart, or supported table. Its buttons represent the distinct values in the field it filters, such as product categories, regions, or months. The slicer’s order is a display choice: changing the order does not change the underlying records or the order in which the PivotTable calculates results.
In Excel for Microsoft 365 on Windows, right-click a slicer and choose Slicer Settings to see its sorting controls. Slicer buttons cannot generally be rearranged by dragging them into a desired sequence. The usual alphabetical order is convenient for names, but it is unhelpful when the values represent a process, a ranking, or a non-calendar sequence such as New, In Progress, Waiting, and Complete.
Custom sorting means telling Excel that the order of those labels has meaning. For example, a quarter slicer should usually show Q1, Q2, Q3, Q4 rather than an order determined by alphabetic comparison. Excel custom lists provide one way to express that sequence; a separate numeric key or model-level sort is useful for more specialized cases.
Sort a slicer with an Excel custom list
A custom list is an ordered series of text entries stored in Excel’s settings. Excel includes common lists, such as weekdays and months, and lets you add your own. A list can tell Excel that workflow stages, priority labels, or fiscal periods should appear in a particular order when sorting is based on the list.
- In Excel for Microsoft 365 on Windows, choose File > Options > Advanced.
- Scroll to the General section and choose Edit Custom Lists.
- In Custom Lists, either select a built-in list or enter your labels in the List entries box, one per line and in the intended order. Choose Add, then choose OK to close the dialogs.
- Right-click the slicer and choose Slicer Settings. Turn on Use Custom Lists when sorting, choose ascending order, and confirm the change.
For example, add the entries Backlog, In Progress, Review, Complete in that order. If the slicer field contains those same labels, Excel can use the custom list rather than ordinary alphabetical order. The labels need to match the values in the source field; extra spaces or different wording can keep an item from matching as expected. Check the slicer after applying the setting, especially if the field also contains values not included in the list.
Custom lists are an Excel application setting, not a sort instruction embedded in the source data. On Windows, they are managed through Excel Options. On Mac, the custom-list controls are in Excel’s settings or preferences, but the exact menu wording can vary by release. If a workbook will be opened by other people, do not assume that a locally created custom list is available on their computer; test the workbook in the environment where it will be used.
Handle orders that do not fit a custom list
Some categories are easier to manage with an explicit sequence number. Suppose a company uses the labels Bronze, Silver, Gold, Platinum, but needs to place them in a different business-defined order. Add a numeric sort key to the source data, such as 1, 2, 3, and 4, and keep the display label in its own field. The key documents the intended order and is less dependent on spelling than a custom list.
For a PivotTable built from the Excel Data Model, you can use the model’s Sort by Column feature. Add the category and its numeric order column to the model, open Power Pivot with Power Pivot > Manage, select the category column, and choose Sort by Column to select the numeric key. The displayed category values can then follow the key’s order in model-based reports, including applicable slicers. Each category should have one unambiguous sort value; duplicate labels assigned conflicting keys cannot have a single consistent order.
For a regular PivotTable that is not using the Data Model, a custom list is often the cleanest way to order the visible labels. Another practical option is to make a slicer from a separate, carefully designed field, but that can change what the buttons mean and how users filter the report. Adding prefixes such as 01, 02, and 03 to labels can force a lexical sequence, but it also exposes those prefixes in the slicer unless you build a separate display field. Use that approach only when the prefixed labels are acceptable to report users.
Choose the right method for common sequences
Pick the sorting method according to what the values represent and how the workbook is maintained:
- Months or weekdays: try Excel’s built-in custom lists and enable custom-list sorting. Confirm that the values are actual text labels that correspond to the list; abbreviated and full names may not behave identically.
- Business stages or priority labels: create a custom list if the sequence is short and stable. For a Data Model report, a numeric order column can make the intended order easier to maintain.
- Fiscal periods or numbered phases: a carefully defined custom list works for a fixed set of labels. A numeric key is more adaptable if periods are added regularly or have complex names.
- Ordinary names or locations: alphabetical sorting is usually the clearest and requires no custom setup.
Dates deserve special care. If the source contains real Excel dates, consider grouping or formatting them as dates rather than storing labels such as Apr, Aug, and Dec as text. A text field can sort alphabetically, whereas real date values have a chronological meaning. If the slicer must display business-specific labels such as FY27 P1, use a deliberate list or a model sort key rather than expecting Excel to infer the fiscal calendar.
Refreshes, source data, and shared workbooks
Sorting a slicer does not repair or reorder the source table. It changes how the slicer presents its items. When new distinct values arrive after a PivotTable refresh, Excel may include them in the slicer, but a custom list cannot place an unlisted label at a meaningful point in the sequence. Add new labels to the custom list or update the sort-key mapping as part of the same maintenance process.
For a source table, keep category labels consistent: avoid accidental trailing spaces, inconsistent capitalization, and near-duplicates such as In Review and In review. Those can appear as separate slicer buttons or fail to match the intended custom-list entry. After correcting source values, refresh the PivotTable using Data > Refresh All or the PivotTable refresh command, then inspect the slicer again.
For a shared workbook, explain the intended order in the workbook or report documentation and keep the list or key with the report’s maintenance steps. A custom list is stored in Excel’s settings, so another user may not have the same list on their machine. Data Model sorting is stored with the model and is generally more portable as part of a workbook, though compatibility and data-source restrictions can still matter. Test the file on the versions and platforms your audience uses.
Common problems and practical fixes
If the slicer remains alphabetical, check that the custom-list checkbox is enabled in that slicer’s settings and that ascending order is selected. Then compare the source labels with the entries in the custom list, paying attention to spaces and spelling. Close and reopen the settings if necessary, and refresh the PivotTable after changing its source values.
If only some values follow the desired order, the list may not contain every label. Excel may still have to position values that are not in the custom list according to its regular sorting behavior. Add the missing entries in their proper positions, or use a different method if the field changes too often for a manually maintained list. Also check that the slicer is connected to the PivotTable you are actually viewing: a workbook can contain multiple PivotTables and slicers, and a slicer’s report connections are managed separately from its sort order.
If the options are missing or do not affect the result, consider the data source. PivotTables based on external OLAP sources or certain model connections can have sorting behavior governed by the source or model rather than local workbook settings. Excel for the web may not expose every desktop setting needed to create or manage custom lists. In those cases, adjust the source or model order in an available desktop version, or ask the data owner to provide a correctly sorted field.
Custom order versus alphabetical sort
Alphabetical sorting is predictable and requires no upkeep, which makes it suitable for independent categories such as city names. A custom list is better when the sequence has meaning but the labels themselves do not encode that order. A numeric key or Data Model sort is usually more maintainable for a growing, structured sequence, particularly when more than one report needs the same business order.
These methods do not all behave alike when labels are added. A custom list needs updating when a new label should be inserted at a specific point. A numeric key requires a reliable mapping from each label to its order. Alphabetical sorting automatically accommodates new names but cannot represent a business-defined sequence. Choose based on how often the category set changes, whether the order must travel with the workbook, and whether users should see any helper values.
Frequently Asked Questions
Can I drag slicer buttons into a custom order?
No. Slicer buttons are not normally reordered by dragging. Use the slicer’s sort settings with a custom list, or use an appropriate sort key or Data Model sort-by-column setup.
Why does my slicer ignore my custom list?
Check that Use Custom Lists when sorting is enabled, the slicer is sorted ascending, and the field labels match the list entries. Also consider whether the PivotTable’s data source controls sorting.
Can I sort months chronologically in a slicer?
Usually. Use the built-in month custom list for matching month names, or use real date values and an appropriate date field. If the labels are fiscal periods, define their intended sequence explicitly.
Will the custom order work for everyone who opens the workbook?
Do not assume so. Custom lists are stored in Excel settings and may not exist on another user’s computer. A Data Model sort-by-column setting is stored with the model, but test the workbook in the versions your audience uses.
Can I use a custom sort in Excel for the web?
The web version may not provide all the desktop controls for creating custom lists and configuring slicer sorting. Set up or troubleshoot the custom order in Excel desktop when those controls are unavailable online.
Bottom line
For a straightforward custom slicer order, define the sequence as an Excel custom list and enable Use Custom Lists when sorting in Slicer Settings. Use a numeric key and the Data Model’s Sort by Column feature when the order is more complex or needs to travel with a model-based report. In either case, keep labels consistent, account for new categories, and check the result after refreshing the PivotTable.
Related
- Excel Protected Sheet: Can a Table Still Grow?
- Excel Protect Sheet Options: What Each Setting Does
- Excel Protect Sheet: How It Works and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Conditional Formatting Formula in Excel: How to Use ItIn Excel, a conditional-formatting equation is a formula that returns TRUE or FALSE; Excel applies the chosen format…
- How to Add Parentheses in Excel Formulas CorrectlyTo add parentheses in an Excel formula, edit the cell, type an opening parenthesis where you want a…
- Power BI XLOOKUP: DAX and Power Query Lookup OptionsPower BI does not have Excel’s XLOOKUP function in DAX. To match a value from another table, use…
- XLOOKUP Examples: Exact, Approximate, and Wildcard MatchXLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For…
- Color Picker in Excel: Choose and Match Colors PreciselyIn Excel for Microsoft 365 on Windows, select the cell or range, then go to Home > Fill…
- How to Consolidate Duplicate Values in Excel with FormulasTo combine repeated labels and total their numbers in Excel, create a PivotTable: select your data, choose Insert…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.