Pivot Table Grand Totals Tips and Tricks

Excel automatically adds grand totals to a pivot table, if there are multiple items in the row area, or in the column area. See how you can change the automatic grand total headings (sometimes), and quickly remove grand totals if you don’t need them.

Change Grand Total Headings

Most of the time, the automatic heading is Grand Total, but sometimes it’s “Total”, followed by a pivot field name.

This video shows when each type of heading appears, and how you can change them.

It also shows why you might see a warning message, saying that you “Cannot edit subtotal, block total, or grand total names.”

For the written steps, and sample file, go to the Pivot Table Grand Totals page, on my Contextures site.

Video Timeline

0:00 Introduction
0:52 Add a Column Field
1:15 Change the Heading Text
1:55 Add Another Value Field
3:01 Change Total Field Heading
3:51 Get the Sample File

Remove One Grand Total

To remove either grand total from a pivot table, use this quick tip

In the pivot table, right-click on a Grand Total heading cell
- Do NOT click on a grand total number cell
In the pop-up menu, click Remove Grand Total

Remove Both Grand Totals

To remove both grand totals, follow these steps to use a command on the Excel Ribbon.

Select any cell in the pivot table
On the Excel Ribbon, click the Design tab
In the Layout group, at the left, click Grand Totals
In the drop down list, click the Off for Rows and Columns command.

Tip: That menu also has commands to turn the Grand Totals on, after you’ve removed them

Grand Total Tricks

The two videos below show tricks for pivot table grand totals. I use these workarounds because there’s no built-in way to do either of these things in a pivot table:

Show Grand Totals at the top of a pivot table
Include multiple Grand Totals in a pivot table

Watch these videos to see the workaround steps, and there are written instructions on the Pivot Table Grand Totals page of my Contextures site, as well as sample files to download.

Grand Totals at Top

This video shows the workaround steps, to create a grand total at the top of the pivot table

Video Timeline

0:00 Introduction
0:41 Source Data – New Field
2:24 Add Field to Pivot Table
2:42 Change Subtotal Setting
2:56 Hide Original Grand Total
3:14 Get the Sample File

Multiple Grand Totals

Use this workaround to show multiple grand totals in a pivot table.

Video Timeline

00:00 Introduction
00:41 Add More Subtotals
01:15 Add Column in Source Data
01:48 Add Grand Total Field in Pivot Table
02:50 Add More Grand Totals

__________________