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
Adding, Removing and Adjusting Subtotals (00:14)
When we have more than one row label, Excel automatically creates subtotals. For our example, our Pivot Table includes salespeople and subscription type as row labels, so we get a subtotal for each salesperson.
By default, the subtotal appears at the top of each salesperson’s figures, which is not very intuitive. To change this, we can view the subtotal options by pressing Alt, J, Y, T, and selecting the option to put subtotals at the bottom of the group.
Adding these subtotals also adds column subtotals, which we don’t want. To remove them, we select a subtotal heading, and press Right Mouse Button Key, B to remove the subtotals.
Percentage of Parent (01:49)
An alternative way of dealing with multiple row labels is to use the percentage of parent options, which can be accessed from the Show Values As menu by pressing Right Mouse Button Key, A. For this example, we switch the two row labels in the Pivot Table to show subtotals for each subscription type.
We select the “% of Parent Row Total” option, which shows the revenue for each salesperson as a percentage of the total revenue for that subscription type. If we only consider subscription types, it shows the revenue for each subscription type as a percentage of the total revenue. This lets us see which subscription types are most important to the business.
In the previous lesson, we learned how to update a PivotTable when new data is added to the dataset.
In this lesson, we'll add subtotals to our PivotTable. When we have more than one field in the row labels area, Excel creates subtotals, which are bullet figures in the PivotTable.
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 sum values. Our PivotTable would be much easier to read if we could move our subtotals below their constituents. To make this change, we'll use the subtotal's dropdown in the design tab of the ribbon. To access this command from the keyboard, we'll press Alt + J + Y to enter the design tab and then T to access the dropdown menu. We'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. Unfortunately, when we add subtotals in this way, Excel automatically add subtotals to the columns as well. If we use the shortcut Alt + A + J to expand our columns, we can see the new subtotals columns 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 PivotTable 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 we switch around the order of our row labels and put subscription above salesperson, we 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 role labels called percentage of parent. The fastest way to access this command is suppressed the right mouse button key and then A for Show Values As. We can now select one of the percentage of parent options. We'll select the first of these and press Enter.
This command displays each row as a percentage of the subtotal. It also 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.
Our 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 dataset.