Sign in or start a free trial to avail of this feature.
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 (00:03)
When sorting a Pivot Table, the selected cell determines which row label is sorted. For example, our Pivot Table contains subscription type and salesperson as its two row labels. If we select a cell that is part of the subscription type label, than any sort of filter will only apply to that row label, and will not sort or filter salespeople. Similarly, if we select a salesperson and perform a sort or filter, then only salespeople will be affected.
Sorting Rows (00:42)
To access the Sort menu, we press Right Mouse Button Key, S. We want to sort our sales people by their total revenue, instead of alphabetically. We do this by going to More Sort Options, then selecting Ascending and the field we want to sort by, which is Sum of Revenue.
Our Pivot Table has five columns, one for each quarter and a grand total. We can actually sort the salespeople by any of these columns. For example, to sort by quarter 3 revenue, we return to More Sort Options, then More Options. We can then use the “Values in selected column“ option to sort by the column of our choice, such as quarter 3 revenue. Note that this sort is automatically removed if you expand the quarter column to view monthly revenue.
Sorting Columns (02:14)
Sorting columns is less common than sorting rows, but is possible. In our Pivot Table, we might want to sort months by total revenue instead of chronologically. To do this, we select a month, go to More Sort Options, and sort ascending by Sum of Revenue. This sorts the months by revenue within each quarter, but not for the full year. If we remove the Quarters label from the Pivot Table, then the table sorts months by revenue for the whole year.
In the previous lesson, we learned how to add subtotals to a pivot table. In this lesson, we'll learn how to sort, and filter data in a pivot table. Pivot tables allow you to sort and filter data as you would in any normal dataset. When performing a filter or a sort, the selected cell is very important. We currently have a cell selected, which is part of the subscription type row label. If we perform a sort or filter, now it will only apply to large, medium and small rows but not to individual salespeople. If we select a salesperson and perform a sort, it will apply to the salespeople in each subscription type but not 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.
We'd like to sort our salespeople by grand total sales, rather than alphabetically. Unfortunately, this option isn't available here. So let's select more sort options. Here we'll select ascending by pressing A, and then Tab to select the sum of revenue. This option will sort our salespeople by grand total. If we press Enter to exit the menu, we can see that our salespeople are sorted by that column.
We can actually sort our salespeople by any of the five column labels.
Let's say we want to sort by total revenue in quarter three. We'll go back to the sort menu with right mouse button S and then M for more sort options.
The solution isn't currently available, so we'll have to press R to go into more options, and then V to enter the values in selected column option.
Now we can select a cell in the quarter three column. For Kroll, this is D eight. We'll then press okay, and press it again.
We can now 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, this sort is obviously removed, and the salespeople are ordered again alphabetically.
Although less common, it's possible to sort columns as well as sorting rows. Let's say we'd like to sort our months by total revenue instead of chronologically. We'll first minimize our salespeople so we can see the grand total row.
Navigate to one of the months, right click S and more sort options. We'll press A for ascending, and then select sum of revenue. When we press okay, the order of the months has changed. Unfortunately, it's ordered our months by revenue within each quarter, but not the full year. To disorder our months from highest to lowest for the full year, we actually have to remove the quarters column label. So we'll drag it out of the area, and now our months are sorted by revenue for the full year.
To sort chronologically again, we'll use right mouse button S to enter the sort menu, and go back to oldest to newest. Let's stop the lesson here. In the next lesson, we'll learn how to apply filters to a pivot table.