Multiple Grand Totals in Excel Pivot Table

✅ Get the sample Excel file to follow along: https://myctx.link/TopGT

In an Excel pivot table, you can show subtotals, to summarize the values by Sum, Count, and other functions. By changing a setting, you can show more than one subtotal for each item.
This option isn't available for grand totals, but with a workaround, you can show multiple lines for a grand total, such as Sum, Average, and Max

💡 Related Links 💡
Pivot Table Grand Totals ► https://myctx.link/TopGT

🔴 Related Excel Videos 🔴
Excel Pivot Table Grand Total Headings ►    • How to Change Grand Total Headings in...  
Grand Total at Top of Pivot Table ►    • How to Show Grand Total at Top of Exc...  

✅ Excel resources I recommend ► https://myctx.link/xlpick

⏰ Video Timeline ⏰
00:00 Introduction
00:41 Add More Subtotals
01:15 Add Column in Source Data
01:48 Add Grand Total Field in Pivot Table
02:50 Add More Grand Totals

-------------------------------------

Video Transcript
In a pivot table, you can use different functions for the values.

By default, the values go in as sum, or count, and here we have a sum of quantity.

The subtotals show the sum for each region, and the grand total is the overall sum.

When we change this function: right click | Summarize Values By | Count.

Now each region shows the count, and the grand total is a count.

I'll go back to Sum again, and we can add more subtotals for each region, but the grand total only shows one row.

So we're going to see how we can add extra subtotals and then a work around for showing multiple grand totals.

For the East region: I'm going to right click on that cell, go down to Field Settings, and in this window (right now, it's set for automatic, which is the sum), I'll select Custom and click on Sum.

I'd also like to see an Average.

When I click OK we can now see two rows for the subtotal.

So here's the sum for the East and the average quantity per order.

The same for the West, the sum and the average, but the grand total doesn't change; It continues to show just the sum.

As a work around, we can go to the source data.

On this StoreSales sheet is the data that I used for the pivot table.

I'm going to insert a column.

I'll click on column C then right-click and Insert.

That puts in a column; It's called column one.

I'm going to call this GrandTotal, and leave the rest of the column blank.

All we need is the heading, which will create a field that we can use as our new grand total.

Going back to the StorePivot sheet, I'll right-click somewhere in this pivot table and click Refresh.

Now that GrandTotal field is showing up in the field list.

I'll drag that, so that it's the first field in the rows area.

You can see it now is blank, because all of the cells in that column are blank, and nothing else has changed.

For this field, instead of it showing blank, I'm going to select that cell and press the space bar.

That makes the cell look empty, but there's a space character in there.

I want to subtotal this row, just like we subtotaled the regions, and then get that subtotal down at the bottom.

With this cell selected, I'll right click and Subtotal "GrandTotal".

So now there's the subtotal at the top and we can see that it's exactly the same as the grand total.

I'd like to move this down to the bottom though, and to do that:

Go to the DESIGN tab | Subtotals | Show all Subtotals at Bottom of Group.

It moves that to the very bottom, so it's right there with the grand total.

We can hide the grand total (move this up a bit so we can see), I'll right click and remove the grand total.

So now we just have our new total that we created, and to get multiple grand totals now, I'll right click | Field Settings, and do the same thing that I did for the regions.

For custom, I'll select Sum and Average, and maybe Max, so we'll have three rows as our grand total, and click OK.

And now for the overall results for our pivot table. We can see the sum, which was what we had before as a grand total, but here's also an average and a max.

So the highest quantity in any order was 460. The average was 48.

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com