Show Grand Total at Top of Pivot Table

In a pivot table, you can choose to show or hide the grand totals, but you can't change their position. However, with a quick and easy workaround (no programming required), you can show the grand total for columns at the top of the pivot table.

Unfortunately, there's nothing similar that will create a grand total for rows at the left side of the pivot table.

Read the Instructions

To see the written instructions, please visit the Contextures website, and go to the Pivot Table Grand Totals page. There is also a sample file on that page, that you can download.

Watch the Video

To see the steps for creating a grand total at the top of a pivot table, watch this short video.

Correction: Important to note that this only works if you have chosen either the “Show in Compact Form” option OR the “Show in Outline Form” option from the Report Layout tab (in Excel 2007/2010). For some reason it doesn’t work when the “Show in Tabular Form” option is selected, which is a shame because that is the option I always use.

Hi
a similar question I think. I like the “top 10” but would REALLY like a “top 10 plus the balance” such that you get the ten rows, plus a sum of all the others and, hence, a total which is the true total of all rather than the total of just the top 10. I think it can be done in VBA with GROUPS but thats a bit ugly as it would need to drop a group and rebuild every time the Pivot refreshed. Is there a better way ? I use Excel 2007. Many thanks for your consideration.

Ahh. Also worth noting that a few GETPIVOTDATA formulas placed above your pivot will perform the same trick e.g. =GETPIVOTDATA(“Total”,$A$3,”Region”,”Alberta”)

Or if you are sure your pivot table won’t be moved, and assuming your column labels start in row B4 then you can drag this across: =GETPIVOTDATA(“Total”,$A$3,”Region”,B4)

Advantages compared to your approach include:
1. Don’t have to add extra column to source data
2. Can use when the “Show in Tabular Form” option is selected
3. Can use a similar approach to create a grand total for rows at the left side of the pivot table using =GETPIVOTDATA(“Total”,$B$3,” “,”Grand Total”,”Item”,”Binder”) – and again if your pivot won’t move then you can replace “Binder” with the cell reference it sits in, and drag down.

Disadvantage of using GETPIVOTDATA include:
1. may break if you’ve used cell references instead of hard-coding field names, and the pivot table is moved or resized. But then you can always use named ranges instead of cell addresses, and trigger some VBA to update named ranges in response to a pivottable structure change.
2. may break if you HAVEN’T used cell references and have instead hard-coding field names. So I’d use named ranges plus VBA to amend them in need.

You can also use a formula in the row between the report filter drop down box and the Row Labels drop down box.
The formula is =VLOOKUP(“Grand Total”,$A$4:$Z$1000,COLUMN(),FALSE). The range $A$4:$Z$1000 is determined by how you pivot table
looks. Then just copy that formula across as many columns as you need.