Excel Pivot Table Sort: Multiple Columns Explained

Charts & pivot tables · Updated November 22, 2026

To sort an Excel pivot table by multiple columns, you apply a custom sort: click any cell in the pivot table, go to the Data tab, click Sort, choose More Sort Options, then select Manual or use the Sort by dropdown to pick the first field, and add a secondary sort by choosing the second field in the Then by list. This works in Excel for Microsoft 365, Excel 2021, and most earlier versions, though the exact dialog wording varies slightly.

Why Sorting a Pivot Table Isn’t Like a Normal Range

A pivot table groups and aggregates data dynamically. Unlike a regular worksheet range where you select columns and click A→Z, a pivot table has two independent axes: row labels (categories) and column labels (field headers). Sorting one field doesn’t automatically reorder another. For example, if you have a pivot with Region in rows and Product in columns, sorting by total sales for Region won’t reorder the Product columns. You must explicitly specify which field to sort and on which axis.

Pivot tables also have a default sort order that follows the source data order (or alphabetical for text). When you drag a field into the Rows area, Excel initially sorts alphabetically. To get a meaningful order—say, by sum of sales descending—you need a value-based sort, which is different from a text sort. The built-in sort buttons on the ribbon (Data > Sort A to Z) only affect the currently selected field’s labels, not the underlying values.

How to Sort by One Column First

Before tackling multiple columns, ensure you know the single-column sort. Click any cell inside the pivot table. On the Data tab, click the Sort button (or right-click and choose Sort > More Sort Options). In the dialog, choose Ascending or Descending and pick the field you want to sort by from the Sort by dropdown. For example, if you have a pivot with Salesperson in rows and Total Sales in values, selecting Total Sales and descending will list the highest salesperson first.

This single sort is the foundation. The multi-column sort is just an extension: after setting the first sort field, you add a second condition. Excel calls this Then by in the sort dialog.

Step-by-Step: Sorting by Multiple Columns

Here’s the exact process for Excel for Microsoft 365 on Windows:

  1. Click any cell inside the pivot table to activate the PivotTable Tools.
  2. Go to the Data tab on the ribbon.
  3. Click the Sort button (it’s in the Sort & Filter group).
  4. In the Sort by dialog, choose More Sort Options at the bottom.
  5. In the Sort options dialog, select Manual if you want to drag items yourself, or select Ascending/Descending to enable the dropdowns.
  6. In the Sort by dropdown, pick the first field (e.g., Region).
  7. Check the box for Then by and select the second field (e.g., Product).
  8. Click OK twice.

On a Mac, the steps are the same: Data tab > Sort > More Sort Options. The dialog layout is identical. In Excel for the web, the sort options are more limited—you can only sort by a single field using the dropdown arrows on the field headers, and manual multi-column sort isn’t available. Use the desktop version for full control.

Using the Right-Click Menu for Quick Multi-Sort

Another way to access multi-column sorting is via the right-click menu. Right-click any cell in the pivot table, choose Sort > More Sort Options. This opens the same dialog. This is faster if you’re already working in the pivot area.

One common misconception: you cannot sort by two different value fields (e.g., sum of sales and sum of profit) in a single sort operation. The sort dialog only allows sorting by label fields or by a single value field. To sort by multiple values, you’d need to add both as fields in the Values area, then use the Sort by dropdown to choose one of them, and the other remains unsorted. Alternatively, you can use a custom list or a calculated field, but that’s beyond basic sorting.

Common Problems and How to Fix Them

  • Sort order resets when you refresh: Pivot tables remember the sort you set, but if you refresh and the source data changes significantly, the sort may revert. To lock it, use a custom list (File > Options > Advanced > Edit Custom Lists) and then assign that list to the field.
  • “Then by” is greyed out: This happens when you select Manual in the sort options. Manual sorting disables the dropdowns. Choose Ascending or Descending to enable them.
  • Sorting by a column that isn’t in the pivot: You can only sort by fields that are currently in the Rows, Columns, or Values areas. If you want to sort by a field not displayed, add it temporarily to the Rows area, sort, then remove it—but that changes the layout.
  • Numbers sort alphabetically (1, 10, 2): This occurs if the source data has numbers stored as text. Convert the source column to actual numbers (use Text to Columns or VALUE function) and refresh the pivot.
  • Sorting by a subtotal column: If you have subtotals, Excel sorts by the subtotal value automatically when you choose a value field. To sort by a specific subtotal, click the subtotal cell first.

Sorting with Custom Lists for Non-Alphabetical Order

Sometimes you want a specific order that isn’t alphabetical or numeric—like Monday, Tuesday, Wednesday. Excel has built-in custom lists for days and months, but you can create your own. Go to File > Options > Advanced > Edit Custom Lists. Enter your list (e.g., High, Medium, Low) and add it. Then, in the pivot sort dialog, choose More Options and under First key sort order, select your custom list. This works for a single field, and you can combine it with a second field using the Then by option.

Frequently Asked Questions

Can I sort by two value fields at once?

No, the standard sort dialog only lets you sort by one value field. If you need to sort by two values (e.g., first by total sales, then by profit), you can add both as value fields, then use a workaround: sort by the first value, then sort by the second while holding the Shift key? That doesn’t work. Instead, create a helper column in the source data that combines both values (e.g., =sales*1000+profit) and sort by that calculated field.

Why does my pivot table sort alphabetically even after I sort by value?

This happens if you have multiple row fields. For example, if you have Region and then Product, sorting by the value field only sorts the outer field (Region). The inner field (Product) remains alphabetical. To sort the inner field, you must select a cell in that field’s column and apply the sort again, or use the Then by option to specify the inner field.

Does sorting in a pivot table affect the source data?

No, sorting a pivot table only changes the display order in the pivot. The underlying source data remains untouched. If you want to reorder the source data, you’d sort the range directly.

How do I sort by a column that is not in the pivot table?

You can’t directly. You must add that field to the Rows or Values area first. For example, if you have Salesperson and Total Sales, but you want to sort by Commission rate (which isn’t in the pivot), add Commission rate to the Values area (as Sum or Average), then sort by that value field, then remove it from the pivot if you don’t want it displayed.

Is there a keyboard shortcut for sorting a pivot table?

There is no dedicated shortcut for the sort dialog. However, you can use Alt + D + S to open the sort dialog in older Excel versions, but in Microsoft 365 it’s Alt + A + S (Data tab, Sort). On a Mac, use Option + Cmd + S? Actually, on Mac the shortcut is Cmd + Shift + S? To be safe, use the mouse or the ribbon.

Can I save a custom sort order for reuse?

Yes, by creating a custom list as described above. Once you create a custom list, it appears in the sort options for any pivot table in that workbook. You can also use it for normal range sorts.

Alternatives: Using Slicers and Report Filters

If your goal is to view data in a specific order, sometimes sorting isn’t the best tool. Slicers and report filters let you isolate a subset of data, which can make the remaining order more meaningful. For example, if you have a pivot with many regions, you can filter to just two regions, and then sorting by sales becomes clearer. Slicers are visual buttons that let you filter quickly. They don’t change sort order, but they reduce clutter. To add a slicer, click anywhere in the pivot, go to PivotTable Analyze > Insert Slicer.

Another alternative is to use the Sort by feature in the field list. In the PivotTable Fields pane, you can drag fields to reorder them, but this is manual, not value-based. For a fully dynamic sort that updates automatically, consider using the SORT function on a separate worksheet that references the pivot table’s values, but that’s a more advanced approach.

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.