How to Sort a Pivot Table in Excel

To sort a pivot table, right-click inside it and use the Sort menu: sort by the values to rank items from largest to smallest, or by the labels to list them A to Z. A pivot table remembers its sort, so the order holds when you refresh. This guide covers both kinds of sort, custom orders, nested fields, and the reasons a sort sometimes seems to do nothing.

The sample pivot table#

The examples use a pivot table that totals sales by region:

By default, Excel lists row labels in A to Z order, as here. That is rarely the order a manager wants. Usually the question is "which region sold the most?", and the answer should be at the top. If you haven't built a pivot table before, start with the pivot table guide for beginners.

How to sort a pivot table by values#

Sort by the numbers to rank items. This is the sort you will use most:

1. Right-click any number in the Sum of Sales column, such as 25,100. On a Mac without a right button, Control-click.
2. Choose Sort > Sort Largest to Smallest.

The rows now read West (31,600), North (25,100), East (18,400) and South (12,750). The Grand Total row stays at the bottom; it is never sorted with the other rows.

You can also click a value cell and use the Data tab: Sort Largest to Smallest (Z to A) does the same job. Either way, click a value in the body of the pivot. Excel sorts by the column you clicked, so if the pivot has two value fields, such as Sales and Units, click the one you want to rank by.

How to sort pivot table labels A to Z#

Sort by the labels when you want an alphabetical list, or to undo a value sort. Click the small arrow next to Row Labels in the header, then choose Sort A to Z or Sort Z to A.

If the pivot uses the Tabular or Outline layout, the arrow sits on the field name, such as Region, instead. You can also right-click any label and choose Sort > Sort A to Z.

Numbers stored as text sort like words, so labels such as 1, 2 and 10 come out as 1, 10, 2. Convert them to real numbers in the source data, then refresh the pivot.

How to sort a pivot table in a custom order#

Sometimes neither A to Z nor largest first is right, for example North, South, East, West. You have three ways to set your own order:

• Drag. Click a label, move the pointer to the cell's border until it becomes a four-headed arrow, and drag the item up or down.
• Use Move. Right-click a label and choose Move > Move "East" Up (or Down, or to the beginning or end).
• Type over a label. Click the cell that shows East and type West. Excel moves West into that position instead of renaming the item.

For an order you use often, such as Small, Medium, Large, create a custom list. On Windows, go to File > Options > Advanced > Edit Custom Lists; on a Mac, Excel > Preferences > Custom Lists (called Settings in newer versions). Type the items in order and click Add. A pivot uses custom lists when it sorts A to Z, as long as Use Custom Lists when sorting is ticked under PivotTable Options > Totals & Filters. It is ticked by default.

How to sort inside groups with nested row fields#

When a pivot has two row fields, such as Region and then Product, the sort works inside each group. Right-click a product's value and choose Sort Largest to Smallest, and each region lists its products from best to worst. The regions themselves don't move.

To rank the regions too, right-click a region's subtotal and sort it the same way. Excel orders the regions by their subtotals and keeps each region's products together underneath. The lesson on nested row fields and subtotals practises exactly this layout.

To sort the columns from left to right, such as months across the top, right-click a value, choose Sort > More Sort Options, and pick Left to right under Sort direction.

How to show only the top items#

Sorting puts the biggest items first, but a long list still shows everything. To show only the top 3, click the arrow on Row Labels, choose Value Filters > Top 10, change 10 to 3, and click OK. Combine it with a largest-first sort for a clean ranking.

Ranking questions like this are common in pivot table interview questions. If you need a ranking in a formula instead, Excel 2021 and Microsoft 365 can do it with SORT, as in the exercise on this page.

Why won't my pivot table sort?#

When a sort seems to do nothing, one of these is usually the cause:

1. You clicked outside the values. A click on a header or a blank cell next to the pivot sorts nothing useful. Click a number in the body of the pivot.
2. A manual order is in place. After you drag items, Excel keeps your order. Open Sort > More Sort Options and choose Ascending or Descending to switch back to an automatic sort.
3. New items land at the end after refresh. In More Sort Options, click More Options and tick Sort automatically every time the report is updated.
4. Months sort in calendar order. Jan, Feb, Mar is a built-in custom list. Untick Use Custom Lists when sorting to sort them alphabetically, though calendar order is usually what you want. For dates, group them by month or year instead.
5. You sorted the wrong field. A pivot with Count and Sum shows two columns. Check which one you clicked; the guide to counting in a pivot table explains how to tell them apart.

Practise it#

A sorted pivot table answers "what's the biggest?" at a glance, and it is a standard task in data and finance tests. Practise building pivot tables in a live spreadsheet: you total sales by region and read the answers off the pivot, with instant checks. Then move on to nested fields and subtotals, or test yourself with the data analyst Excel test.