Excel Pivot Table Grand Total Headings

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: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 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.

grandtotallabel08

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”.

grandtotallabel01

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:

  1. Select either one of the “Grand Total” heading cells
  2. 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.

grandtotallabel02

As soon you complete the name change, Excel automatically changes the other Grand Total heading, to the same text.

grandtotallabel03

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.

grandtotallabel06

Cannot Change Total Field Headings

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.”

grandtotallabel04

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.

grandtotallabel07

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.

_______________

Save

This entry was posted in Group and Total. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *