When a pivot table has grand totals, Excel automatically names those totals. I’ll show you some examples, with details on which grand total headings you can change, and which ones you can’t.
Grand Total Headings Video
This video shows how pivot table Grand Totals are created, and how you can change some of the headings. The detailed instructions are below the video.
The Video Timeline:
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 Excel Workbook
Creating Grand Totals
If you create a very simple pivot table, with one Row field and one Value field, Excel will automatically show a Grand Total of the amounts at the bottom.
Then, if you add a field to the Columns area, Excel will show a Grand Total at the right. The headings both have the default text – “Grand Total”.
Change the Grand Total Headings
Sometimes you might want a different Grand Total heading, instead of the default text. For example, if the column contains small numbers, you could put a shorter name at the top. That will let you keep the pivot table as narrow as possible.
In a simple pivot table like this, it’s easy to change the grand total headings – just follow these steps:
- Select either one of the “Grand Total” heading cells
- Then, to change the text:
- Type a new heading, to replace the existing heading
- OR Press F2, then edit in the text in the cell
- OR Click in the Formula bar, and edit the text there
NOTE: You can’t double-click the cell to edit the text.
In the screen shot below, I typed “GT” in cell D4, to create a smaller heading in column D.
As soon you complete the name change, Excel automatically changes the other Grand Total heading, to the same text.
Grand Total Headings – Multiple Values
If there are multiple Value fields in a pivot table, Excel shows a different set of grand total headings.
In the screen shot below, the Order Count field was added to the Values area. Now, instead of a Grand Total column at the right, there are columns with “Total” and the field names – Total Qty and Total Orders
The Row Grand Total, in cell A11, did not change.
It was easy to change the original “Grand Total” headings, but Excel won’t let you change these “Total Field” headings
If you try to edit a Total Field heading, an error message will appear: “Cannot edit subtotal, block total, or grand total names.”
Can Still Change Grand Total Headings
However, that error message is a bit misleading. You can’t change the “Total Field” headings, but you CAN still edit the “Grand Total” heading in that pivot table.
In the pivot table shown below, I was able to change the Row “Grand Total” heading, without any problems.
More Pivot Table Grand Total Info
For more information, go to the pivot table Grand Totals page on my Contextures website. See workarounds for showing a grand total at the top or at the left, and other grand total tricks.