7. Pivot Table Subtotals
Subtotals are created when you have more than one row or column label. Learn how to create and format them correctly in this lesson
- By default subtotals are arranged above their constituents
- Placing subtotals below these entries is much more intuitive
Percentage of Parent
- Once you have more than one column / row label, the % of Parent option can be used
Useful keyboard shortcuts
ALT + J, Y, T: Access Subtotals dropdown menu
RMBK, B: Remove the selected subtotal row / column
RMBK, A: Access Show Values As menu
ALT + J, T, A: Access Show Values As menu (alternative shortcut)
When we have more than one field in the Row labels area, Excel creates subtotals, which are the bolded figures in the Pivot table.
In this scenario, they correspond to the total revenues for each sales person.
Unfortunately, the position of these subtotals is not very intuitive, because the subtotal appears above the summed values.
Our Pivot table would be much easier to read if we could move our subtotals below their constituents.
To make this change, we’ll use the Subtotals drop-down in the Design tab of the ribbon.
To access this command from the keyboard, I'll press Alt + J Y to enter the Design tab, and then T to access the drop-down menu.
I’ll now select the option that puts subtotals at the bottom of the group and press Enter.
This creates a new row at the bottom of each group that contains much more intuitive subtotal positions.
When we add subtotals in this way, unfortunately Excel automatically adds subtotals to the columns also.
If I use the shortcut Alt + A J to expand our columns, you can see the new subtotal columns that have been added.
To remove these, we can simply press the right mouse button key under Heading, and then press B to remove the subtotals.
Whenever a Pivot table has more than one role label, I would always recommend making this simple format change, as it can avoid a lot of errors when reading values from the table.
If I switch around the order of our role labels and put Subscription above Sales person, you can see how the formatting change makes this arrangement more readable as well.
Now let’s take a look at another function we can use with multiple row labels called Percentage of Parent.
This command appears in the Show Values as drop-down, we looked at in our lesson on changing the Output values.
We could access this by pressing Alt + J T in the Options tab in the ribbon, but a quicker way is to press the right mouse button key and A for Show values as.
We can now select one of the Percentage of Parent options.
I’ll select the first of these and press Enter.
This command displays each row as a percentage of the subtotal, and it displays the subtotals as a percentage of the grand total.
Manipulating the data in this way can give some useful insights into our business.
For example, it appears as if small companies contribute very little to our overall revenue.
The company might be better off focusing future sales and marketing exclusively on large and medium customers which make up the bulk of the overall revenue.