Workaround to show a grand total at the top of a pivot table, or show multiple grand totals. Change grand total headings.
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..
After you add the Grand Total (GT) field, change its settings so the amounts show at the top.
With the new Grand Total at the top, you can turn off the default grand total at the bottom.
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:
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:
Then, when you filter with a Slicer, both pivot tables will change, and you will see the grand totals at the left.
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:
In this example, the source data is a formatted Excel table, on another sheet in the pivot table's workbook.
Use a workaround to show multiple grand totals in a pivot table. Watch this video to see the steps, and written instructions are above.
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 - 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.
If there is only one Value field, the default heading is Grand Total, for both the Row and Column Grand Total headings.
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.
To change the Row or Column "Grand Total" text, follow these steps:
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.
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.
1. For the Grand Totals at the Top tutorial, download the zipped sample file in xlsx file format. The file does not contain macros. The sample file is also available in Excel 2003 format (xls).
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.
Last updated: March 27, 2022 12:39 PM