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.


14 comments to Show Grand Total at Top of Pivot Table

  • Jeff Weir

    Huh. Well, well, well. Never knew that one. Important to note that this only works if you have chosen the "Show in Compact Form" option from the Report Layout tab (in Excel 2007/2010).

    Thanks Deb.

  • Jeff Weir

    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.

    • Thanks Jeff, I forgot to mention that, and have it in the written instructions. In Tabular form, the outer field starts in the same row as the inner field, so subtotals are only allowed at the bottom.

      • Gerry Perry

        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.


  • Jeff Weir

    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.

  • Maxime Manuel

    I do not understand it. There is no explanation of how to do this. IS it in Excel 2007 and up?

  • Jeff Weir

    Ahh, yes...a seperate pivot table is much better.

  • Maxime Manuel

    Thank you Debra! I will look at them a bit later and enjoy the tutorial. :)

  • Amy Swayzee

    Debra, you are always my go-to person for excel tips. Thanks again for such an easy fix for something that I would not have thought of and certainly needed!

  • Hi
    I couldn't get this to work when I had 2 value fields (ie. two fields being summed) - with sigma values in the rows.
    It was ok with just one value ,not with 2

  • Jim Brass

    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.

    Looks like this: copied formula across Row 2 as far as needed

    Col A Col B Col C
    1 Reports Filter
    2 Grand Total =VLOOKUP("Grand Total",$A$5:$BZ$1000,COLUMN(),FALSE) =VLOOKUP("Grand Total",$A$5:$BZ$1000,COLUMN(),FALSE)
    3 Rows Label

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>