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:
The rows in the pivot table are now sorted by Sum of Points from largest to smallest, then by Sum of Assists from largest to smallest.
Lastly, you can right click column H and then click Hide from the dropdown menu to hide this calculated field from the pivot table:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sum Two Columns in a Pivot Table in Excel
How to Subtract Two Columns in a Pivot Table in Excel
Excel: Find Percentage Difference Between Two Columns in Pivot Table
I have a spreadsheet of songs & artists. My pivot table sorts by count of songs, for each artist.
What I’d LIKE is: when I get down to 1-hit wonders, the artists should be in alphabetic order. By this article, I need to convert artist names to a numeric value- how would you suggest I do that?
Hi Alan…To sort your pivot table by multiple columns (e.g., count of songs first, then artist names alphabetically for 1-hit wonders), you don’t need to convert artist names to numeric values. Instead, you can use Excel’s built-in sorting functionality. Here’s how you can achieve your desired outcome:
### Steps to Sort the Pivot Table:
1. **Add a Secondary Sorting Column**:
– In your data source (not the pivot table), create a new column next to the artist names and copy the artist names there. This will serve as your secondary sorting key.
2. **Update the Pivot Table**:
– Refresh your pivot table so it includes the new column as a field.
– Add this new column (with artist names) as a row field in the pivot table, below the primary field (e.g., “Count of Songs”).
3. **Sort by Count, Then Alphabetically**:
– Click the dropdown arrow on the “Count of Songs” field in the pivot table.
– Choose “Sort Largest to Smallest” (or whichever order you prefer).
– Next, click the dropdown arrow on the new column (artist names).
– Sort alphabetically (A-Z).
### What If Excel’s Built-in Sorting Isn’t Enough?
If you still need a numeric representation of artist names:
1. **Create a Helper Column**:
– Use a formula to assign numeric values based on artist names. For example, you can generate numeric values using ASCII codes:
“`excel
=SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))
“`
Here, replace `A1` with the cell containing the artist name. This formula sums up the ASCII values of each character in the name.
2. **Include the Helper Column in the Pivot Table**:
– Add this numeric column to your pivot table and sort by it as needed.
This ensures that artist names are assigned a numeric value if required, but it’s generally not necessary for your sorting needs. Excel’s default sorting mechanisms should suffice.