Excel: How to Sort Pivot Table by Multiple Columns

Typically when you create a pivot table in Excel you’re only able to sort by one column at a time.

For example, if we have the following pivot table then we would only be able to sort by either Sum of Points or Sum of Assists, but not by both columns:

However, we can use the Calculated Field option to create a custom column that we can sort by and then delete.

The following step-by-step example shows how to do so.

Step 1: Enter Dataset

First, we’ll enter the values for a dataset that contain information about various basketball players:

Step 2: Create the Pivot Table

Next, let’s create the following pivot table that summarizes the sum of the points and assists for each team:

Step 3: Add Calculated Field to Pivot Table

Suppose we would like to sort the rows in the pivot table first by Sum of Points from largest to smallest, then by Sum of Assists from largest to smallest.

To do so, we need to add a calculated field to the pivot table by clicking on any value in the pivot table, then clicking the PivotTable Analyze tab, then clicking Fields, Items & Sets, then Calculated Field:

In the new window that appears, type “CustomOrder” in the Name field, then type = (Points *1000) + (Assists/1000) in the Formula field.

Then click Add, then click OK.

This calculated field will automatically be added to the pivot table:

Note that this formula creates a new value that scales the points value extremely high and then scales the assists value extremely low.

This creates a new value that, when sorted, is equivalent to sorting by points from largest to smallest and then by assists from largest to smallest.

Step 4: Sort Based on Calculated Field

Next, we can right click on any value in the new Sum of CustomOrder column, then click Sort from the dropdown menu, then click Sort Largest to Smallest: