8. Sorting Pivot Table Data
Pivot Tables come with lots of sorting options for both columns and rows. Learn how to access these options in this short lesson
Sorting Pivot Table data
- The selected cell is determines which field is sorted
- The most common sort options are alphabetically or by sum of revenue
- However, you can specify the column you want to sort by in More options
- Sorts are removed when you expand / collapse a group
Useful keyboard shortcuts
RMBK, S: Access the Sort menu
ALT + A, J: Expand entire field
ALT + A, H: Collapse entire field
Pivot tables allow you to sort and filter data as you would in a normal data set.
When you’re performing a Filter or a Sort, the currently selected cell is very important.
I currently have a cell selected which is part of the Subscription type row label.
If I perform a Sort or a Filter now, it will only apply to the Large, Medium and Small rows, but not to the individual sales people.
If I now select a sales person and perform a Sort, it will apply to the sales people in each Subscription type, but not to the Large, Medium and Small rows.
If you remember this simple concept, sorting and filtering in a pivot table should not be a problem.
Let’s access the Sort menu from the keyboard by pressing the right mouse button key and S.
I’d like to sort our sales people by Grand Total sales rather than alphabetically.
Unfortunately, this option isn’t available here, so let’s select more Sort options.
And here I’ll select Ascending by pressing A and then Tab, to select Sum of Revenue.
And this option will sort our sales people by Grand Total.
If I press Enter to exit the menu, you can now see that our sales people are sorted by that column.
We can actually sort our sales people by any of the five column labels.
Let’s say I wanted to sort by total revenue in quarter three.
I’ll go back into the Sort menu, with right mouse button S and then M for more Sort options.
The solution isn’t currently available, so I’ll have to press R to go into more options.
And then I’ll press V to enter the values in selected column option.
And now I can select a cell in the quarter three column.
For Kroll this is D8.
I’ll then press OK, and press it again, and now we can see that our sales people are sorted correctly for quarter three revenue.
If you’re sorting by quarter and you decide to expand the columns, by pressing Alt + A J, the Sort is obviously removed, and the sales people are ordered again alphabetically.
Although less common, it’s possible to sort columns as well as sorting rows.
Let’s say I’d like to sort our months by Total Revenue instead of chronologically.
I’ll first minimize our sales people so that you can see the Grand Total row, and now I’ll navigate to one of the months, right click S and more Sort options.
I’ll select A for Ascending and select Sum of Revenue.
And when I press OK, the order of the months has changed, but unfortunately it's ordered our months by Revenue within each quarter, but not for the full year.
To sort our months from highest to lowest for the full year, we actually have to remove the Quarters column label.
So I’ll drag it from out of the area, and now our months are sorted by Revenue for the full year.
To sort chronologically again we can right mouse button S to enter the Sort menu and go back to Oldest to Newest.
Now that I’ve shown you how to sort Pivot table data, let’s move on to Filtering in the next lesson.