PivotdrillRemove grand totals and subtotals
Click in the pivot, open Design, Grand Totals and choose Off for Rows and Columns; for subtotals open Design, Subtotals and choose Do Not Show Subtotals. Both are toggles, so the same menus bring the totals back.
Syntax
Design > Grand Totals > Off for Rows and Columns; Design > Subtotals > Do Not Show Subtotals| Part | What it is |
|---|---|
On for Rows and Columns | Default: a bottom Grand Total row and a right Grand Total column |
On for Rows Only | Keep the bottom row, drop the right column |
On for Columns Only | Keep the right column, drop the bottom row |
Show all Subtotals at Top / Bottom | Where group subtotals sit |
Examples
Rows: Region. Columns: Product. Values: Sum of Units
Design > Grand Totals > On for Rows OnlyResult: The bottom total per product stays; the right-hand total per region disappears.
Rows: Region, Rep. Values: Sum of Revenue
Design > Subtotals > Do Not Show SubtotalsResult: No East Total, North Total and so on; only the rep rows and the Grand Total remain.
Right-click the Grand Total cell > Remove Grand TotalResult: Same as the Design menu, one click.
Common mistakes
- Grand Total shows a wrong number for Average: the total row averages all source rows, not the row averages, which is correct but often surprising.
- A subtotal still appears for one field: subtotals are per field, so right-click that field's label and untick Subtotal.
- Turning off totals removes the % of Grand Total base: no, the percentages still use the full total; only the display row goes.
Practice Remove grand totals and subtotals
These drills check your answer on live data, free, no account needed.
- Region, then repShow total Revenue by Region, and within each region by Rep.
- Region by product gridShow total Revenue with Region down the side and Product across the top.
Questions
How do I remove the grand total from a pivot table?
Design, Grand Totals, Off for Rows and Columns, or right-click the Grand Total label and choose Remove Grand Total.
Can I show subtotals with a different function?
Yes. Right-click the field, Field Settings, Custom, and pick one or more functions; subtotals can show Sum and Average at once.
Related
- Two row fieldsPut two or more fields in Rows to nest categories, choose which field is outer and inner, collapse groups and switch to tabular layout for separate columns.
- Tabular layoutSwitch a pivot table to Tabular Form so every row field gets its own column, compare Compact, Outline and Tabular, and make Tabular the default layout.
- Percent of grand totalShow each row as a share of the overall total with Show Values As, % of Grand Total. Add it next to the raw numbers, format it, compare with parent totals.
Previous: Repeat row labelsNext: Blank cells in a pivot table