Sort Pivot Table by Value

There may be cases when you want to sort a pivot table by values and not by labels. For example, you may want to sort names by earnings, with the biggest number listed first.

This is the table we are going to use.

It shows a list of people, their age, and their earnings. Let’s create a pivot table from this data. Drag the fields into the fields.

After you do it you are going to get the following pivot table. You can change header names, but they have to be different than

By default, names are listed in alphabetical order.

If you want to sort a pivot table by value, click a cell inside a table you want to sort, and navigate to Home >> Editing >> Sort &  Filter and choose whether you like to sort it from the largest to the smallest or vice versa.

You can do the same with Age. Just click a cell under the Sum of Age and choose sorting.

Let’s move Age from values to columns.

The pivot table is going to change.

By default, ages will be sorted from the lowest to the highest number. Let’s change that and display the lowest earnings to the left and the highest one to the right.

We are going to use the Custom Sort. You can find it under Home >> Editing >> Sort & Filter.

Click any Age cell inside a pivot table and then choose Custom Sort.

Inside the window, under the sort options, choose ascending sort by Sum of Earnings.

After you click ok, the ages with the lowest earnings will be on the left.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.