Home > Pivot Tables > Grand Totals Pivot Table Grand TotalsWorkaround 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 TopThere'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
Create fake "Grand Total" field
|
Change Field SettingsAfter you add the Grand Total (GT) field, change its settings so the amounts show at the top.
Hide original Grand TotalWith the new Grand Total at the top, you can turn off the default grand total at the bottom. In Excel 2007 and Excel 2010:
|
Show Multiple Grand TotalsIn 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 "Grand Total" fieldIn this example, the source data is a formatted Excel table, on another sheet in the pivot table's workbook.
Show Subtotals for New Field
Select Multiple Functions
Hide original Grand Total |
Grand Total HeadingsIf 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. Video Timeline
|
Remove Grand TotalIf 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 ColumnTo remove pivot table Grand Total column follow these steps:
Remove Grand Total RowTo remove pivot table Grand Total row follow these steps:
Remove Both Grand TotalsTo remove both pivot table Grand Totals, for the Column and the Row, follow these steps:
|
Show Grand TotalsIf 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:
|
Get the Sample Files1. 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. |
Last updated: September 12, 2022 1:45 PM