Controlling totals and sub-totals in a PivotTable.
Where/when to use the technique
Use to turn totals and subtotals on/off where needed in your PivotTable.
Create your PivotTable
- Begin by creating a PivotTable with at least 2 row fields and 2 column fields
- Notice that subtotals are automatically added
Show Subtotals Below Data
- Go to PivotTable Tools Design tab, Subtotals, Show all Subtotals at Bottom of Group
Turning Subtotals on/off
- Individual subtotals: Right click row (or column) label on the PivotTable and choose to check/uncheck the “Subtotal <field name>”
- All subtotals: Go to the PivotTable Tools Design tab, Subtotals, Do Not Show Subtotals
Multiple Levels of Subtotals
- Right click the row label and choose Field Settings
- Change “Automatic” to Custom and click all the subtotals you wish to display
Keep in mind
- You can turn subtotals back on at any time
- Collapsing rows/columns will display the subtotaled values
- Grand total commands are found under PivotTable Tools Design tab, Grand Totals
- Can hide or show grand totals for both columns and rows at once
- Can hide or show grand totals for columns only (shows in final row of Pivot Table)
- Can hide or show grand totals for rows only (shows in final column of Pivot Table)
Lesson notes are only available for subscribers.