Subtotals and Parameters

Subtotals and Parameter Order

Previous Session Summary

In the previous lesson, we looked at how to add multiple fields into the Rows and Columns quadrants, creating hierarchies of fields.
The Pivot Table then changed its summarising calculations and layout depending on which fields were in each Quadrant as well as how the fields were arranged in each Quadrant, i.e. which field was above or below another field.

Lesson Synopsis

In this lesson we look further at how moving the fields around each quadrant can affect the layout of the pivot table and understand why Excel presents the Pivot Table in different ways depending on the layout of fields in each quadrant.

We also look at removing subtotals, how to delete a pivot table and how to ensure that any cells brought in by the fields in Rows or Columns quadrants are correctly labelled with no blanks.


Resource Listing

The Video

Play Video

Lesson Notes

During the last lesson, we set up our Pivot Table so that the 2016 information is grouped together by month. To the right of the 2016 information is the 2017 information, grouped together in the same way. That means, in order to compare the same month year on year, we need to shift from one side of the screen to the other. We can change that though by putting the same month in both 2016 and 2017 next to each other.

To do that, simply click and hold on the Year field in the Columns quadrant and drag it below the month field. The pivot table will then reorganise itself into having the month side by side instead of having the year side by side. The reason this happens is because Excel will always sort data on the first parameter it finds in each of the row and column quadrants, and then will sort data according to the next parameter and the next one and so on.

In the previous incarnation of the pivot table, the Year parameter was the first criteria that we asked the pivot table to sort by, so Excel split the data into the two separate years and then sorted the months in each year. When we have the Month parameter first, Excel sorts the data by month first and then by year second.

Excel also automatically adds in subtotals for fields that are not the lowest level of granularity (the lowest field) in the Quadrant. In our example, Month is the higher level of granularity and Year is the lower. However, subtotals can be removed.

To do this, go to the relevant Quadrant (in our case Columns), then to the Month field, and then left click on the field and select “Field Settings”. A pop up menu appears and the first tab commands the field’s subtotals and filters. In the first section which is Subtotals, we have three options, Automatic, which is the current setting, None which will remove all subtotals and custom, where we can choose the subtotal column to do something other than SUM the totals in the years. We want the “None” option. Once selected, our subtotal columns disappear.

You may have noticed that that there are no months above any of the columns marked 2017. There’s a couple of ways to sort this out.

Firstly you can go back to the Field Settings option that we just used by selecting the Month Field in the Columns quadrant, and then selecting “Field Settings”. Then instead of staying on the Subtotals and Filters tab, swap to the Layout and Print tab and select “Repeat Item Labels”. Now the names of all the Months appear on top of each year as well.

There is another way to do this too. Select the Design tab (the Pivot Table must be selected) and then on the ribbon, select “Report Layout” and “Repeat all Item Labels”.

To delete the Pivot Table highlight the rows the pivot table is in and then right click the highlighted rows and select Delete.

.... and a little more!

Subtotals and Multiple Fields –  In our examples we only look at having two fields in a quadrant. However, subtotals can be brought in or removed for every field in the quadrant that is not the bottom field in the quadrant (the lowest level of granularity in the pivot table). For example, if we were to have Partner, Country and Segment all in the Rows quadrant, with Segment being the bottom field in the Quadrant, Excel would create subtotal rows for both Partner and Country. We could then remove the subtotal row for either Partner or Country, by going to the relevant field in the Rows quadrant, left clicking the field and selecting Field Settings. From there, in the Subtotal and Filters tab, shift the Subtotals option to None.The subtotal will then disappear from the relevant field but will remain in place for the unselected fields.