Last updated: March 17, 2009 7:10 PM
Search Contextures Sites ![]()
Excel -- Pivot Tables -- Grand Totals
Show Grand Total at the Top of a Pivot Table
There's no setting that allows you to display the grand total at the top of a pivot table. However, with this technique you can use another field that acts as the grand total, and display it at the top.Note: The instructions and sample file are for Excel 2003 and earlier.
Download the zipped sample fileCreate the fake "Grand Total" field
- In the source data, add a column with the heading GT, or use a space character as the column heading
- In every row of the source data, for the GT field, enter: Grand Total
or leave the column blank, except for the heading.
- Add this field to the pivot table, as the first field in the Row area
Change the Field Settings
- In the pivot table, right-click on the GT field button, and click Field Settings
- Click the Layout button
- Click Show Items in Outline Form
- Add a check mark to Display Subtotals at Top of Group
- Click OK, twice.
Hide the original Grand Total
Show Multiple Grand Totals
There's no setting that allows you to display multiple grand totals in a pivot table. However, with this technique you can use another field that acts as the grand total, and display multiple Grand Totals, such as the Sum and Average overall.Note: The instructions and sample file are for Excel 2003 and earlier.
Download the zipped sample file
Create the fake "Grand Total" field
- In the source data, add a column with the heading GT, or use a space character as the column heading
- In every row of the source data, for the GT field, enter: Grand Total
or leave the column blank, except for the heading.
- Add this field to the pivot table, as the first field in the Row area.
Change the Field Settings
- In the pivot table, right-click on the GT field button, and click Field Settings
- Click the Layout button
- Click Show Items in Tabular Form
- Click OK.
Select Multiple Functions
- Under Subtotals, select the summary functions that you want for the multiple subtotals, e.g. Sum and Average.
- Click OK
Hide the original Grand Total