How to sort a pivot table by value

In many cases, you’ll want to sort pivot table items by values instead of labels. For example, you might want to sort products by total sales, with the best-selling products listed first. This is easily done.

Let’s take a look.

Let’s quickly build a pivot table that shows total sales and order count by product. As usual, products are listed in alphabetical order by default.

To sort a pivot table by value, just select a value in the column and sort as you would any Excel Table. We can do the same thing with Orders. Let’s sort orders in descending order. As always, we can hover over the sort icon to see the currently applied sort options.

Let’s remove Sales, and add City as a column label. By default, cities are shown in alphabetical order, but we can easily sort City by Orders, with greater values to the left.

One way to sort columns by values is to use the large Sort button on the Options tab of the PivotTable tools ribbon. Just select a City, and click the Sort button. In this case, we want to sort in descending order, by Orders. Note that the title of this dialog box shows that we are sorting the City field.

When we click OK, the cities with the most orders are at the left. Products are still sorted by Orders as well, with greater values at the top.

In addition to the ribbon, you can also access sort options using the field drop-down menu. Both options take you to the same place.

You can also sort by value at more than one level. If we remove City and add Category above Product, we see that Category is in alphabetical order, while Product remains sorted by Orders. Let’s sort Category to match Product.

Note that you can also sort Category and Product by Orders just by selecting a value in the pivot table and sorting. For example, we can select a Category subtotal, and sort only by Category. And we can do the same thing with Product.