How to display multiple subtotal rows in a Microsoft Excel PivotTable

Use this hard-to-find setting to display multiple subtotals in your Microsoft Excel PivotTables.

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, which return totals, counts, and averages. By default, Excel displays a SUM() function for subtotals and grand totals, but you might encounter situations where you need more than one subtotal and grand total.

In this tutorial, I’ll show you how to display multiple subtotals in a PivotTable. The method is not intuitive, so it’s possible that you don’t even know you can do it.

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.

How to create a PivotTable in Excel

Now let’s say you’re tracking sales for several cities in the US and you want to see subtotals for the five regions. Figure A shows the source data and the first PivotTable you might design.

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. Using Figure A as a guide, create this PivotTable by dragging fields into the appropriate sections.

Before we continue, let’s change the structure a bit to make the PivotTable a bit more readable:

1. Click in the PivotTable and then click the Contextual Design tab.
2. In the Layout group, choose Show All Subtotals At The End Of Group from the Subtotals drop-down menu.
3. From the Report Layout drop-down list, select View in Tabular Form.
4. From the Report Layout drop-down list, select Do Not Repeat Item Labels.

The resulting PivotTable, shown in Figure B, is more readable than the standard structure. This isn’t required, but I believe this format is a bit cleaner and easier to read.

Figure B

The PivotTable still shows subtotals for each region and a grand total. What if you want to see the average sale for each region? In this case, you need a second subtotal line.

How to add a second subtotal row to a PivotTable in Excel

The default PivotTable is useful, but you may need more information. For example, let’s add a second subtotal row that returns the average sale for each region. You could add Amount to the Values ​​list in the Fields area and then set it to Average instead of Sum, but this will add another column with the same values. Honestly, the results would keep viewers busy and distracted.

Instead, do the following:

1. In the PivotTable, click any cell in the Region column.
2. Click the PivotTable Analysis contextual tab.
3. In the Active Field group, click Field Settings.
4. In the result, in the Subtotals section, click Custom.
5. In the function list, choose Sum and Average (Figure C).
6. click OK.

Figure C

Figure D

You just added an average row to the PivotTable as shown in Figure D. This process isn’t particularly intuitive and is compounded by the requirement that you select Sum and Average. If the subtotal row already exists, you can select Average only, which would replace the total row instead of inserting an additional average row.

stay tuned

Excel PivotTables are flexible. The only thing not available is a way to add more total rows. In a future article I will show you how to do this.