To sort pivot table by value you first need to select a cell from the Values field instead of category items in Row Labels field. If not, you may end up sorting the Row Labels field from A to Z or Z to A, which may have been sorted that way to begin with.

Data Source

The data in the pivot table comes from https://data.gov.au/data/dataset/airport-traffic-data and contains all domestic and international flight volume data from January 1985 to April 2020 for 20 airports in Australia.

The screenshot above shows an unformatted pivot table which is already sorted by Airport name.

Sort by Value

To sort the second column by value, right-click on a cell in second column’s value field that is not a grand total.

Select Sort, then choose either “Sort Smallest to Largest” or “Sort Largest to Smallest”. The “More Sort Options” option simply gives additional optional to sort by column instead of by row.

Alternatively, select a cell in second columns’ value field then go to Home tab, select Sort & Filter icon to access the same options.

The sort operation will affect other columns. Unlike a normal sort of data, you cannot do multiple layers of sort. Instead, there is no indication of which value field is sorted and you cannot simultaneously sort another column. This sort operation is called a pivot table sort.

If you need to do multiple layers of sort on a pivot table, consider pasting the pivot table to another worksheet by value.

If you have text in value field, you will be given the option to sort A to Z or Z to A instead.

Pin It on Pinterest

Share This