Microsoft Excel PivotTables are a great way to summarize data. They’re easy to create and display information in meaningful ways, such as subtotals and grand totals that return sums, counts and averages. By default, Excel displays a SUM() function for subtotals and grand totals, but you might run into 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 isn’t intuitive, so it’s possible that you might not even know you can do this.
SEE: 108 Excel tips every user should master (TechRepublic)
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 suppose you track sales for several cities across 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.
To create this PivotTable, click anywhere inside the data source Excel Table and then do the following:
- Click Insert.
- Inside the Tables group, click PivotTable.
- In the resulting dialog, click Existing Worksheet, and then click G2 in the Location control.
- Excel will insert a blank frame. Using Figure A as a guide, build this PivotTable by dragging fields to the appropriate sections.
Before we continue, let’s change the structure a bit to make the PivotTable a little more readable:
- Click inside the PivotTable and click the contextual Design tab.
- In the Layout group, choose Show All Subtotals At The Bottom Of The Group from the Subtotals dropdown.
- From the Report Layout dropdown choose Show In Tabular Form.
- From the Report Layout dropdown, choose Do Not Repeat Item Labels.
The resulting PivotTable, shown in Figure B, is more readable than the default structure. Doing so isn’t necessary, but I believe this format is a bit cleaner and easier to read.
As is, the PivotTable displays subtotals for each region and a grand total. What if you want to see the average sale for each region? In this case, you’ll need a second subtotal row.
How to add a second subtotal row to a PivotTable in Excel
The default PivotTable is useful, but you might need more information. For instance, 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 pane and then set it to average instead of sum, but doing so will add another column of the same values. Frankly, the results would be busy and distract viewers.
Instead, do the following:
- Click any cell in the Region column in the PivotTable.
- Click the contextual PivotTable Analyze tab.
- In the Active Field group, click Field Settings.
- In the resulting, click Custom in the Subtotals section.
- From the function list, choose Sum and Average (Figure C).
- Click OK.
You just added an average row to the PivotTable, as shown in Figure D. This process isn’t particularly intuitive, and it’s compounded by the requirement that you select Sum and Average. With the sum subtotal row already in place, you might select only Average, which would replace the sum row instead of inserting an additional row of averages.
Excel PivotTables are flexible. The one thing that’s not available is a way to add more grand total rows. In a future article, I’ll show you how to do so.