Microsoft Excel Pivot tables are a great way to summarize data. They are easy to create and display information in meaningful ways, such as subtotals and grand totals for sums, counts, and averages. The article Displaying multiple subtotal rows in a Microsoft Excel PivotTable shows how to insert multiple subtotal rows into a PivotTable without repeating data. In this tutorial, I’ll show you how to add multiple grand totals to a pivot table.
SEE: Google Workspace vs. Microsoft 365: a side-by-side analysis with checklist (TechRepublic Premium)
I use Microsoft 365 on a Windows 10 64-bit system, but you can use older versions of Excel. Excel for the web supports pivot tables. YYou can download the Microsoft Excel demo file for this tutorial.
How to make a pivot table in excel
Let’s say you’re tracking sales for several cities in the US and you want to see subtotals for regions. Image A shows the source data and the first pivot table you could design. As you can see, Excel displays a SUM() function for subtotals and grand totals by default, but you may encounter situations where you need more than one grand total.
Image A
To create this pivot table, click anywhere in the data source Excel table and do the following:
1. Click Insert.
2. In the Tables group, click PivotTable.
3. In the resulting dialog box, click Existing Worksheet and then click G2 in the Location control.
4. Excel will insert a blank frame. Using the list of fields shown in Image A As a guideline, build this pivot table by dragging fields into the appropriate sections.
The grand total at the bottom is a sum by default. Let’s add a second total row representing the average sales.
Add multiple total rows to a pivot table in Excel
When you create a pivot table, Excel inserts a grand total at the bottom that returns the sum of the value column. Sometimes you may need a second or even several grand total calculations such as sum, average and so on.
The good news is that you can display multiple grand totals. The bad news is that the process is not intuitive and requires several steps.
To start, go back to the source data and add a column (Figure B). Change the header to GrandTotal and leave the data cells blank. To insert the column, right-click the header cell of column D (Region) and choose Insert from the resulting submenu. I purposely omitted the space character between Grand and Total to make it easier to distinguish between this new Total row and others.
Figure B
Right-click a cell in the pivot table and choose Refresh to add the new field (Figure C).
Figure C
Drag the new GrandTotal field to the Rows list and make sure it’s at the top of the list (Figure D). The result is a new row at the top of the pivot table. The region cell is displayed (blank) and the amount cell displays the same as the total row at the bottom, 657704. Otherwise nothing changes.
Figure D
Then move the new row to the bottom of the pivot table as follows:
1. Click in the pivot table and click the contextual design tab.
2. In the Layout group, click the Subtotals drop-down list.
3. Select Show all subtotals at the bottom of the group.
Doing so moves the subtotal rows to the bottom of their groups. As you can see in Figure Eit also displays the new total row based on the GrandTotal column at the bottom of the pivot table.
Figure E
This next step seems counterproductive, but delete the new row at the bottom of the pivot table:
1. Select and right click on the Total Total row, the last row in the pivot table.
2. Choose Delete Grand Total from the resulting submenu.
3. Select the row above and replace (empty) with Grand.
As you can see in Figure Fthe row has disappeared and the row above it now shows the grand total.
Figure F
If you’re feeling a little lost, don’t worry, we’re almost done. We managed to display a new subtotal at the bottom of the pivot table, but Excel knows that the amount calculation is grand totals and not subtotals.
Now we are ready to add the multiple grand total rows as follows:
1. Right-click the Grand Total cell at the bottom of the pivot table.
2. Choose Field Settings.
3. In the resulting dialog box, click Custom.
4. In the Select one or more functions list, click Sum, Average, and Max (Figure G).
Figure G
5. Click OK to see the new grand total rows at the bottom of the pivot table.
As you can see in figure H, the pivot table has three total rows at the bottom. And remarkably, they calculate grand totals instead of subtotals.
figure H
At this point, you may want to replace the (blank) text in the total rows. To do this, go back to the top of the pivot table and select the cell that is displayed (blank) and replace it with a space. You can’t leave it blank. The cell appears blank and the pivot table is no longer displayed (blank) in the total rows. Figure I shows the new grand total text, without (blank) and the currency format in the amount field.
Figure I
This solution is a bit complicated and not one that most users would encounter by accident. Despite the many steps it works.
Source link
