14.3 C
New York
Thursday, October 6, 2022

Buy now

How to display grand total rows in Excel PivotTable

Microsoft Excel spreadsheet on a computer screen.
Image: wachiwit/Adobe Stock

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

The first pivot table shows one subtotal for each region and one grand total that sums all values.
The first pivot table shows one subtotal for each region and one grand total that sums all values.

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

Add a new field to the source table.
Add a new field to the source table.

Right-click a cell in the pivot table and choose Refresh to add the new field (Figure C).

Figure C

Refresh the pivot table.
Refresh the pivot table.

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

Drag the new field to the Rows list.
Drag the new field to the Rows list.

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

The Total Total row is now at the bottom of the pivot table and displays Grand Total.  Excel seems to know that this is a grand total and not a subtotal.
The Total Total row is now at the bottom of the pivot table and displays Grand Total. Excel seems to know that this is a grand total and not a subtotal.

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

Delete the new TotalTotal row at the bottom of the pivot table.
Delete the new TotalTotal row at the bottom of the pivot table.

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

Select the features you want to see at the bottom of the pivot table.
Select the features you want to see at the bottom of the pivot table.

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

Add total rows to the bottom of the pivot table.
Add total rows to the bottom of the pivot table.

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

The completed pivot table has three total rows instead of just one.
The completed pivot table has three total rows instead of just one.

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

Source link

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay Connected

0FansLike
3,517FollowersFollow
0SubscribersSubscribe

Latest Articles