Excel Pivot Table Secondary Sort: How to Do It

Charts & pivot tables · Updated November 22, 2026

To apply a secondary sort in an Excel pivot table, you sort by one field first, then add a second field to the same sort level using the Sort dialog box (Data tab > Sort, or right-click > Sort > More Sort Options). This lets you order by two fields simultaneously, such as sorting by Region first and then by Sales within each region.

What “Secondary Sort” Means in a Pivot Table

In a normal Excel range, sorting is straightforward: you pick a column and the rows reorder. In a pivot table, sorting is more nuanced because the table has multiple dimensions—row labels, column labels, and values. A secondary sort means you want to order the data by two criteria: for example, first by product category, then by total sales within each category. This is not the same as sorting by two columns in a flat list; it requires telling Excel which field takes priority and which is secondary.

How to Perform a Secondary Sort in Excel (Step-by-Step)

Here’s the exact process for Microsoft 365 on Windows. The steps are identical on Mac, except where noted.

  1. Click anywhere inside the pivot table to activate the PivotTable Analyze tab.
  2. Click the Sort button (in the Sort & Filter group) or right-click a cell in the row/column field you want to sort and choose Sort > More Sort Options.
  3. In the Sort dialog, choose Manual (if you want to drag items) or select Ascending/Descending for the primary field.
  4. Click the Sort by drop-down and select the first field (e.g., Region).
  5. Then, in the same dialog, click the Then by drop-down (if available) and select the second field (e.g., Sales). If you don’t see “Then by,” it means your pivot table layout doesn’t support multi-level sorting—see the next section.
  6. Click OK. The pivot table will now sort by the primary field, and within each primary group, by the secondary field.

On Mac, the Sort dialog is similar, but you may need to use the Sort button in the Ribbon (under PivotTable Analyze). The “Then by” option is present in the same dialog.

Why the “Then By” Option Sometimes Disappears

The “Then by” field only appears when your pivot table has multiple row fields or multiple column fields. For example, if you have only one row field (say, Product), you cannot add a secondary sort because there is nothing else to sort within that field. To get a secondary sort, you must have at least two fields in the Rows area (or two in Columns). If you have two row fields, the secondary sort works within the outer field’s groups. If you have only one row field, you can simulate a secondary sort by adding a second row field that is hidden or by using a calculated field—but that is a workaround, not a true secondary sort.

Sorting by Values Instead of Labels

Often, “secondary sort” means sorting by a value field (like Sum of Sales) rather than by a label. To sort by values, you can use the same Sort dialog: choose the value field in the “Sort by” drop-down. For example, to sort products by total sales descending, select “Sum of Sales” in the Sort by box. Then, if you have a second row field, you can also add a “Then by” value. This is common when you want to rank categories by sales and then rank products within each category by sales.

Common Problems and Solutions

  • Sort order resets after refreshing: Pivot table sorts are not remembered when you refresh data. To keep a custom sort, you can use a custom list or reapply the sort after each refresh (or use a macro).
  • Manual sort doesn’t stick: If you drag items manually, Excel may revert to alphabetical when you refresh. To prevent this, set the sort to “Manual” and then right-click > Sort > More Sort Options > Manual, and ensure “Sort automatically every time the report is updated” is unchecked.
  • Sorting by two fields but not getting the expected order: Check the field order in the Rows area. The secondary sort works within the outer field. If you have Region first and Product second, sorting by Sales will sort products within each region, but regions themselves will be sorted by the first sort criterion. To sort regions by total sales, you need to sort by the value field on the Region field, not on the Product field.
  • “Then by” is grayed out: This happens when you have only one row field. Add another field to Rows, or use a different approach like sorting by a helper column in the source data.

Alternative: Using the Sort Options in the Field List

You can also right-click a field name in the PivotTable Field List and choose Field Settings > Layout & Print > Sort. This opens the same Sort dialog. This is handy when you want to set a default sort for a field that will apply every time you refresh. However, this method only allows one sort criterion per field, so for a true secondary sort you still need to use the More Sort Options dialog.

When to Use a Secondary Sort vs. a Custom Sort Order

A secondary sort is ideal when you have hierarchical data, like Region > Product or Category > Subcategory. A custom sort order (using a custom list) is better when you want a non-alphabetical order, like sorting months chronologically (Jan, Feb, Mar) or priority levels (High, Medium, Low). You can combine both: set a custom list for the primary field, then use a secondary sort by value. For example, sort months by a custom list (Jan, Feb, …) and then within each month sort by sales descending.

Frequently Asked Questions

Q: Can I sort a pivot table by two value fields?
Yes, but only if you have at least two row fields. In the Sort dialog, you can set “Sort by” to one value field and “Then by” to another value field. For instance, sort by Sum of Revenue descending, then by Sum of Profit descending.

Q: Why does my secondary sort not work when I have only one row field?
Because there is no inner field to sort within. You need at least two row fields (or two column fields) for a secondary sort to make sense. If you only have one, consider adding a second field that you can later hide, or use a calculated field to combine criteria.

Q: How do I keep the secondary sort after refreshing the pivot table?
Excel does not remember manual sorts after a refresh. You can set a default sort in Field Settings, but that only applies one criterion. For a true secondary sort that persists, you’ll need to reapply it after each refresh, or use a small VBA macro to set the sort order automatically. Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.

Q: Is there a way to sort by a custom list and then by a value?
Yes. Set the primary field’s sort to “Manual” and drag items in the order you want (or use a custom list), then in the same Sort dialog, add a “Then by” value field. The custom order will be preserved for the primary field, and the secondary sort will apply within each group.

Q: Does the secondary sort work the same in Excel for the web?
Mostly, but the Sort dialog in Excel for the web is simpler and may not show the “Then by” option. In the web version, you can sort by one field only. For a secondary sort, you’ll need to use the desktop app or rearrange the fields to achieve a similar effect.

Conclusion

Secondary sorting in pivot tables is a powerful way to organize hierarchical data, but it requires at least two fields in the Rows or Columns area. Use the Sort dialog with “Then by” to set the order, and remember that sorts may reset on refresh—plan accordingly. With these steps, you can control the exact order of your pivot table data.

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.