Home > Pivot Tables > Grand Totals

Pivot Table Grand Totals

Workaround to show a grand total at the top of a pivot table, or show multiple grand totals. Change grand total headings. Remove grand totals.

Show Grand Total at Top

There's no setting that allows you to display the grand total at the top of an Excel pivot table. However, with the technique in this tutorial you can use another field that acts as the grand total, and display it at the top.

This short video shows the steps for creating a grand total at the top of the pivot table, and the written instructions are below the video.

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

Create fake "Grand Total" field

1. In the source data, add a column with the heading GT, or use a space character as the column heading.
   Note: If your source data is in a named Excel table, you cannot enter a formula in the heading row. If you are not using a named table, use a formula to create a blank space, as shown in the screen shot below: =" "
2. In every row of the source data, for the GT field, enter: Grand Total, or leave the column blank, except for the heading.
   
3. Refresh the pivot table, and add the grand total field, as the first field in the Row area

Change Field Settings

After you add the Grand Total (GT) field, change its settings so the amounts show at the top.

1. Select a cell in the pivot table, and in the Excel Ribbon, under PivotTable Tools, click the Design tab.
2. Click Report Layout, and select Compact Form or Outline Form. (In Tabular Form, subtotals are only shown at the bottom.)
3. Click Subtotals, and click Show all Subtotals at Top of Group

Hide original Grand Total

With the new Grand Total at the top, you can turn off the default grand total at the bottom.

In Excel 2007 and Excel 2010:

1. Select a cell in the pivot table, and in the Excel Ribbon, under PivotTable Tools, click the Design tab.
2. Click Grand Totals, and click On for Rows Only

Show Grand Totals at Left

There's no setting that allows you to display the grand total at the left side of an Excel pivot table, before all the other totals. However, you could use the following workaround, if you:

• use Slicers to filter the pivot table
• do not need to change the pivot table layout after setting it up.

In this technique, you'll create another copy of the pivot table, then hide columns in both pivot tables, so simulate grand totals at the left of the data.

To set up the grand totals at the left:

1. Add Slicers to the pivot table, to filter the fields that you want filtered.
2. Add a few columns to the left of the existing pivot table (enough columns for all the row fields and grand totals)
3. Copy the existing pivot table, and paste it onto a blank sheet
4. In the copy, remove any column fields, and leave just the row fields and grand totals.
5. Move the copy to the pivot table sheet, to the left of the current pivot table.
6. In the current pivot table, remove the Grand totals, and hde the column with the Row items.
7. Leave one very narrow blank column between the two pivot tables, and hide or delete any other blank columns.

Then, when you filter with a Slicer, both pivot tables will change, and you will see the grand totals at the left.

Video: Show Multiple Grand Totals

Use a workaround to show multiple grand totals in a pivot table. Watch this video to see the steps, and written instructions are below.

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

Show Multiple Grand Totals

In a pivot table, you can show multiple subtotals per field, but there's no setting that allows you to show multiple grand totals in a pivot table. As a workaround, you can use another field that acts as the grand total, and display multiple Grand Totals, such as the Sum and Average overall.

To create this workaround, follow the steps below, to:

• add a new field to the source data,
• put it into the pivot table, and
• remove the existing Grand Total.

Add a "Grand Total" field

In this example, the source data is a formatted Excel table, on another sheet in the pivot table's workbook.

1. In the source data, insert a new column, and change the column heading to "Grand Total"
2. Leave the column blank, except for the heading.

   

3. Refresh the pivot table, so the new field appears in the Field List
4. Add the Grand Total field to the pivot table, as the first field in the Row area.
5. The field label shows "(blank)", so select the label cell, and type a space character.

Show Subtotals for New Field

1. In the pivot table, right-click on the new field's label cell, and click Subtotal "Grand Totals"
2. To show the subtotals at the bottom of the pivot table, click the Design tab on the Ribbon.
3. Then, click the Subtotals button, and click Show all Subtotals at Bottom of Group

Select Multiple Functions

1. In the pivot table, right-click on the new field's label cell, and click Field Settings
2. Under Subtotals, click Custom, and then select the summary functions that you want for the multiple subtotals, e.g. Sum and Average.
3. Click OK

Hide original Grand Total

1. Right-click on the Grand Total label cell at the bottom of the Pivot Table
2. Click Remove Grand Total

Grand Total Headings

If Grand Totals are showing in a pivot table, Excel creates default labels as headings for those totals. You can change some of these headings, but not all of them.

Headings - One Value Field

Headings - Multiple Value Fields

Change the Grand Total Headings

Cannot Change Total Field Headings

This video shows the different types of grand totals, and how to change some of the headings. Written instructions are below the video, and you can download the free workbook to follow along.

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

Headings - One Value Field

If there is only one Value field, the default heading is Grand Total, for both the Row and Column Grand Total headings.

Headings - Multiple Value Fields

If there are multiple Value fields, a Values button appears in the Pivot Table Field List, and a Values heading is added to the pivot table.

If the Values button is in the Columns area, the column grand total headings are shown as Total [Field Name]. In the pivot table shown below, the grand totals for the Columns are Total Qty and Total Orders.

The area without the Values button shows the normal "Grand Total" heading. In the pivot table shown below, Row area still shows Grand Total.

Change the Grand Total Headings

To change the Row or Column "Grand Total" text, follow these steps:

1. Select either 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.

The other Grand Total will automatically change, and will show the same text as the heading that you edited.

Cannot Change Total Field Headings

If the grand total heading is a "Total Field" heading, instead of "Grand Total", you won't be able to change the text.

If you try to edit a Total Field heading, an slightly misleading error message will appear: "Cannot edit subtotal, block total, or grand total names."

Despite the warning in that error message, you can still edit the "Grand Total" heading in that pivot table, if there is one.

In the pivot table shown below, the Row grand total can be changed, without any warning message. That change does not affect the Column grand totals.

Remove Grand Total

If Excel adds an automatic Grand Total Row or a Grand Total column, you can remove those totals, if you don't need them. The steps below show the details.

Remove Grand Total Column

To remove pivot table Grand Total column follow these steps:

• In the pivot table, right-click on the Grand Total column heading cell
  • Do NOT click on one of the amount cells in the column
• In the pop-up menu, click Remove Grand Total

Remove Grand Total Row

To remove pivot table Grand Total row follow these steps:

• In the pivot table, right-click on the Grand Total row heading cell
  • Do NOT click on one of the grand total values in the row
• In the pop-up menu, click Remove Grand Total

Remove Both Grand Totals

To remove both pivot table Grand Totals, for the Column and the Row, follow these steps:

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

Show Grand Totals

If the Grand Total Row or Grand Total column have been removed from a pivot table, you can show those totals again, if you need them.

To show Grand Total Row or Grand Total Column later, follow these steps:

• 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 one of the "On" commands:
  • On for Rows and Columns
  • On for Rows Only
  • On for Columns Only

Get the Sample Files

1. For the Grand Totals at the Top tutorial, download the zipped sample file in xlsx file format. The file does not contain macros.

2. For the Multiple Grand Totals tutorial, download the zipped sample file in xlsx file format. The file does not contain macros.

3. To follow the Grand Total Headings tutorial, download the zipped sample file, in xlsx format. The file does not contain macros.