Subtotals and Parameters

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.