How to display grand total rows in Excel PivotTable

Microsoft Excel PivotTables are a great way to summarize data. They are easy to create and display information in a meaningful way, e.g. B. Subtotals and grand totals for totals, counts, and averages. The article How to Display Multiple Subtotal Rows in a Microsoft Excel PivotTable shows you how to insert multiple subtotal rows in a PivotTable without repeating data. In this tutorial, I’ll show you how to add multiple grand totals to a PivotTable.

SEE: Google Workspace vs. Microsoft 365: A Parallel Analysis with Checklist (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use older versions of Excel. Excel for the web supports PivotTables. YYou can download the Microsoft Excel demo file for this tutorial.

How to create a PivotTable in Excel

Let’s say you’re tracking sales for multiple cities in the US and want to see subtotals for regions. Figure A shows the source data and the first PivotTable you might design. As you can see, by default Excel displays a SUM() function for subtotals and grand totals, but you might come across situations where you need more than one grand total.

Figure A

To create this PivotTable, click anywhere in the data source Excel table and then do the following:

1. Click Insert.

2. In the Tables group, click PivotTable.

3. In the dialog box that appears, click Existing Worksheet, and then click G2 in the Location control.

4. Excel inserts an empty frame. Use the field list shown in Figure A As a guide, create this PivotTable by dragging fields into the appropriate sections.

The grand total below is a total by default. Let’s add a second total row that shows the average sale.

How to add multiple grand total rows to a PivotTable in Excel

When you create a PivotTable, Excel inserts a grand total at the bottom, which returns the sum of the value column. Sometimes you may need a second or even multiple grand total calculations like sum, average, etc.

The good news is that you can view multiple grand totals. The bad news is that the process is counterintuitive and requires multiple steps.

First, return to the source data and add a column (Figure B). Change the heading to GrandTotal and leave the data cells blank. To paste the column, right-click the column D (Region) header cell and select Paste from the resulting submenu. I intentionally omitted the space between Grand and Total to make it easier to distinguish between this new Grand Total line and others.

Figure B

Right-click any cell in the PivotTable and select Refresh to add the new field (Figure C).

Figure C

Drag the new field GrandTotal to the Rows list and make sure it is at the top of the list (Figure D). The result is a new row at the top of the PivotTable. The region cell shows (blank) and the amount cell shows the same as the grand total row below, 657704. Nothing else changes.

Figure D

Next, move the new row to the bottom of the PivotTable, like this:

1. Click in the PivotTable and then click the Contextual Design tab.

2. In the Layout group, click the Subtotals drop-down menu.

3. Select “Show all subtotals at the end of the group”.

This moves the subtotal rows to the bottom of their groups. As you can see in it Figure Ealso displays the new grand total row based on the GrandTotal column at the bottom of the PivotTable.

Figure E

This next step seems counterproductive, but remove the new row at the bottom of the PivotTable:

1. Select the Grand Total row, the last row in the PivotTable, and then right-click it.

2. From the resulting submenu, select Remove Grand Total.

3. Select the line above and replace (blank) with Grand.

As you can see in it Figure Fthe line has disappeared and the line above it now shows the grand total.

Figure F

If you’re feeling a little lost, don’t worry because we’re almost done. We managed to display a new subtotal at the bottom of the PivotTable, but Excel knows that the amount calculations are grand totals, not subtotals.

Now we can add the multiple grand total rows as follows:

1. Right-click the Grand Total cell at the bottom of the PivotTable.

2. Choose Field Settings.

3. In the dialog box that appears, click Custom.

4. In the Select one or more functions list, click Sum, Average, and Max (Figure G).

Figure G

5. Click OK to view the new total rows at the bottom of the PivotTable.

As you can see in it Figure H, the PivotTable below has three grand total rows. And remarkably, they calculate grand totals instead of subtotals.

Figure H

At this point you might want to replace the (empty) text in the grand total rows. To do this, go back to the top of the PivotTable, select the cell that appears (blank) and replace it with a space. You can’t leave it empty. The cell appears blank and the PivotTable no longer appears (blank) in the grand total rows. Figure I displays the new total text, sans (blank), and currency format in the amount field.

Figure I

This solution is a bit complicated and not one that most users would stumble upon by accident. Despite the many steps, it works.