Show Multiple Grand Totals in an Excel Pivot Table

In a pivot table, you can show multiple subtotals for a field, such as Sum, Count and Average. However, there isn’t a setting that lets you show multiple Grand Totals.

With a simple workaround, you can replace the Grand Total with a new field, and show two or more Grand Total calculations. In the screen shot below, the Sum, Average and Max are being calculated.

grandtotaladd03

Add a New Field in the Source Data

This solution uses a new field in the pivot table’s source data, with a heading cell, and nothing else in the column.

grandtotaladd01

That field is added to the pivot table, and subtotals are moved to the bottom of each group.

grandtotaladd05

For written instructions, please visit my Contextures website: Pivot Table Grand Totals

Watch the Video

To see the steps for showing multiple grand totals, please watch this short video tutorial.

_______________________

5 thoughts on “Show Multiple Grand Totals in an Excel Pivot Table”

  1. Debra, Is this still possible in Excel 2013. I do not see the custom option in Field Value Settings when I right-click on the label for the grand totals.

  2. I was wondering if you could advise on how I can show the grand total of my pivot table to show the sum of the average amount in my pivot table. In other words, the pivot table has average in the columns and the grand total will show the total of the averages.

    Regards.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.