Sign in or start a free trial to avail of this feature.
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 salesperson. 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 press Alt, J, Y, to enter the design tab, and then T, to access the drop-down view. I'll now select the option to put 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 on the heading, and then press B, to remove the subtotals. Whenever a pivot table has more than one row 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 row labels, and put subscription about salesperson, 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. The fastest way to access this command is to press the right mouse button key, and then 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. As an exercise, try changing around the row label fields, and the percentage of parent options, to see if you can draw some other useful insights from our data set.