Excel Pivot Table Custom Sort Order: How to Set It

Charts & pivot tables · Updated November 19, 2026

To give an Excel PivotTable a custom item order, either drag its row or column labels into the sequence you want, or create an Excel custom list and enable it for PivotTable sorting. For a sequence you will reuse, such as Jan–Dec or Low–Medium–High, a custom list is usually the better choice; for a one-off arrangement, manual sorting is quicker.

What a custom PivotTable sort does

A PivotTable normally sorts text labels alphabetically and numbers numerically. That is useful for names and amounts, but it can be wrong for labels with a meaningful sequence. For example, a report might need sales stages ordered Prospect, Qualified, Proposal, Closed rather than alphabetically, or weekdays ordered Monday through Sunday rather than Friday through Wednesday.

A custom sort changes the display order of the items in a PivotTable field. It does not change the source data, the underlying values, or the calculation of totals. The same source records can therefore appear in a different order in the PivotTable without being rearranged in the worksheet table.

There are three practical approaches:

  • Manual sorting: drag items into place for an individual report.
  • Custom lists: define a reusable sequence in Excel and use it when sorting matching PivotTable items.
  • Helper fields: add an explicit rank or sequence to the source data when the order must be controlled independently of labels.

Set a custom order by dragging items

Manual sorting is the fastest method when the list is short and the order is specific to one PivotTable. Click a label in the row or column area, then drag it to the position you want. In a dense PivotTable, point at the edge of the selected item until the move pointer appears before dragging; otherwise, Excel may select a cell or range instead of moving the item.

  1. Click a cell in the PivotTable field you want to reorder.
  2. Check that the field is displayed as row or column labels, not only as a value.
  3. Drag an item up or down, or left or right for a column field, until it reaches the desired position.
  4. Repeat for the other items, then check the subtotals and grand total to confirm the report still reads correctly.

Manual moves are independent of the source-table row order. They are also field-specific: rearranging the Product field does not define a custom order for Region. If dragging is awkward, right-click an item and inspect the Sort options available for that field. The precise commands depend on the PivotTable layout and Excel build.

Manual order is convenient but requires maintenance. If a refresh introduces a new item, Excel has no business rule telling it where the item belongs. It may appear at the end or be placed according to the current sort behavior. Recheck the sequence after refreshes, especially when a new category is important to the report.

Use an Excel custom list for a repeatable sequence

A custom list stores an ordered set of text entries in Excel, so the same sequence can be used in multiple worksheets and workbooks on that computer. Excel includes familiar lists such as weekdays and months; you can also create your own, for example Bronze, Silver, Gold. Custom lists are most useful when PivotTable labels match the entries in the list.

In Excel for Microsoft 365 on Windows, create a list from the ribbon using File > Options > Advanced. Scroll to the General section and select Edit Custom Lists. In the Custom Lists dialog, either type the entries in the List entries box, one per line, and select Add, or select a worksheet range and use the import control. Close the dialogs with OK to save the list.

To apply a list when sorting a PivotTable, click inside the PivotTable and open PivotTable Analyze > Options. In the PivotTable Options dialog, go to Totals & Filters and select Use Custom Lists when sorting. Then sort the relevant field. Depending on the field and current layout, right-click one of its labels and choose Sort, or use the field’s sort controls. Confirm that the labels appear in the intended list order.

For example, suppose the source contains the labels Low, Medium, and High. Add a custom list with those entries in that order, enable custom-list sorting for the PivotTable, and sort the priority field. The PivotTable can then show Low, Medium, High even though ordinary alphabetical sorting would put High first. If a field contains labels that are not in the list, do not assume Excel will place every unmatched label exactly where you want it; inspect the resulting order and use manual sorting or a helper field for exceptions.

Choose the right method for your report

The best approach depends on how often the sequence is used, who maintains the workbook, and whether new items are expected. A custom list is compact and reusable, but it is stored as an Excel application setting rather than as a visible rule in the source table. A colleague opening the workbook on another computer may not have the same custom list configured. Manual ordering is easy to see in the report, but can take repeated adjustment.

  • Use manual sorting for a short, fixed report that one person maintains.
  • Use a custom list for familiar or recurring text sequences, such as months, weekdays, or defined workflow stages.
  • Use a helper field when the sequence is part of the data definition, must travel with the workbook, or needs to be shared consistently.
  • Use ordinary sorting when the order should reflect the label alphabetically or a value from largest to smallest.

A helper-field method makes the rule explicit. For instance, add a StageOrder column to the source data with 1 for Prospect, 2 for Qualified, 3 for Proposal, and 4 for Closed. You can use StageOrder as an outer row field and Stage as the nested field, then sort StageOrder ascending. This exposes the rank in the layout, so you may need to adjust the report design. In models that support sorting a text column by a separate column, a data-model solution can keep the rank out of the visible report, but that is a different setup from ordinary PivotTable sorting.

Sort labels differently from values

A common confusion is the difference between sorting the items in a field and sorting those items by a measure. For example, a PivotTable with sales regions in rows and Revenue in Values can list regions alphabetically, or sort regions by their revenue totals. Sorting by revenue does not create a semantic order such as West, Central, East; it ranks the regions according to the numbers.

To rank by a measure, use the sort options for the row or column field and specify the value field when Excel offers that choice. This is useful for questions such as which product has the highest sales. To impose a fixed business sequence, use a custom list, manual moves, or a rank field instead. When a PivotTable has several value fields, check which value Excel is using as the sort basis; choosing the wrong measure can make a correct sort appear wrong.

Dates and numbers also have their own sorting behavior. If month names are stored as text, alphabetical order is not calendar order. If the source has real Excel dates, grouping or sorting by the date field is generally more reliable than treating month names as labels. Likewise, numbers stored as text may sort lexically—for example, 1, 10, 2—rather than numerically. Fixing the source data type is often better than trying to force a custom order around a data-quality issue.

Keep the order after refreshing

Refreshing a PivotTable updates its cache from the source data, but it does not make a custom sequence a permanent rule for every possible new item. A manual arrangement may remain for existing items, while a newly added category needs to be placed. A custom list can help with new labels that are included in that list, provided the PivotTable is set to use custom lists when sorting.

After adding source records, use PivotTable Analyze > Refresh to update the selected PivotTable, or Data > Refresh All to refresh workbook connections and PivotTables. Then check the field order. If a sort was changed or reset, apply the intended sort again. For a report that is refreshed by several people, write down the sequence in the workbook or store ranks in the source data rather than relying only on a local custom-list setting.

Sorting choices can also be affected by the source labels. Leading or trailing spaces, inconsistent spelling, and alternate labels such as In progress versus In Progress may create separate PivotTable items. Clean or standardize the source values when they are meant to represent the same category. A custom list cannot resolve the underlying ambiguity in the source data.

Common problems and practical fixes

  • The items still appear alphabetically: confirm that the custom list is saved, that Use Custom Lists when sorting is selected in PivotTable Options, and that you actually sorted the relevant field after enabling the option.
  • The field sorts by sales rather than by label order: open the field’s sort options and check whether sorting is based on a value field. Choose the label/custom-list ordering or use manual sorting instead.
  • A new label appears in an unexpected position: add it to the custom list in the correct place, refresh and sort again, or maintain a rank in the source data.
  • Dragging does not move an item: make sure you are dragging a PivotTable item rather than a worksheet selection, and try a right-click Sort command if the layout is crowded.
  • The same label appears more than once: inspect the source for spaces, spelling differences, or inconsistent data types, then refresh after cleaning it.

Excel for the web can work with PivotTables, but creation and management of custom lists is a desktop Excel feature. For a workbook that depends on a custom list, set up and verify the order in the desktop application. On Mac, Excel has corresponding PivotTable and custom-list settings, but the names and locations can vary by release; look in Excel’s settings/preferences for custom lists and use the PivotTable options available on the PivotTable tab. The instructions above give the Microsoft 365 Windows paths.

Frequently Asked Questions

Can I sort a PivotTable in a custom order?
Yes. Drag the items into order for a one-off arrangement, or create an Excel custom list and enable Use Custom Lists when sorting in PivotTable Options.

How do I sort months chronologically instead of alphabetically?
Use real date values in the source and group or sort the date field when possible. If the PivotTable uses month-name text, an Excel custom list containing January through December can provide the calendar sequence.

Will my custom order survive a PivotTable refresh?
Existing manual ordering often remains, but refreshes can add items or expose changed sort behavior. A custom list or source rank is more dependable for repeatable ordering; check the result after refreshing.

Can I sort a PivotTable by a value and still use a custom order?
Those are different sort rules. Sorting by a value ranks items by a measure, such as total sales; a custom order follows a fixed sequence of labels. Choose the rule that answers the report question.

Why does a custom list not sort my PivotTable?
Check that the entries match the PivotTable labels, the correct field is being sorted, and the PivotTable option to use custom lists is enabled. Also verify that the field has not been sorted by a value instead.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.