Power Pivot Principles: Hiding Subtotals

Welcome back to the Power Pivot Principles blog. This week, we will share a tip on how to hide subtotals in a PivotTable.

If you followed our blogs in the last two weeks, we created the slicers for columns, where we selected which column attributes we wanted to view in our PivotTable.

Let’s continue. In the PivotTable, we want the subtotal rows to be displayed. To do this, click on the PivotTable and navigate to the Design contextual tab on the Ribbon, in the Subtotals group on the left-hand side. Then, choose ‘Show all Subtotals at Bottom of Group’.

Both subtotals for columns and rows are shown. The subtotal column is not necessary and, in any case, is not making sense when we sum all of Actual, Budget and Variance.

Unlike the option with ‘Grand Totals’ where we can select On or Off for Columns or Rows, for Subtotals, we need to tinker more with the settings. To hide the subtotal columns, we click one of the Job cells, say, cell C14, and navigate to the ‘PivotTable Analyze’ contextual tab on the Ribbon. In the ‘Active Field’ group on the left-hand side, select ‘Field Settings’. A ‘Field Settings’ dialog will appear, in the ‘Subtotals & Filters’ tab. Here, we select ‘None’ and click OK.

The subtotal columns are now hidden.

That’s it for this week.

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.