How to display multiple subtotal rows in a Microsoft Excel PivotTable

If you want to show several subtotals in your Microsoft Excel PivotTables, discover how to utilize a hard-to-find setting.

Image: sdx15/Adobe Stock Microsoft Excel PivotTables are an excellent method to summarize data. They’re easy to create and show information in meaningful methods, such as subtotals and grand totals that return amounts, counts and averages. By default, Excel displays an AMOUNT()function for subtotals and grand overalls, but you may face circumstances where you require more than one subtotal and grand total. In this tutorial, I’ll show you how to show numerous subtotals in a PivotTable. The approach isn’t intuitive, so it’s possible that you might not even understand you can do

this. SEE: 108 Excel tips every user ought to master (TechRepublic)I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use older variations of Excel. Excel for the web supports PivotTables. How to develop a PivotTable in Excel Now let’s expect you track sales for a number of cities throughout the US and you wish to see subtotals for the five regions. Figure A reveals the source data and the very first PivotTable you might create. Figure A This default PivotTable displays subtotals for each region and a grand total for all regions.

To produce

this PivotTable, click anywhere inside the information source Excel Table and then do the following

: Must-read Windows coverage Click Insert. Inside the Tables group, click PivotTable. In the resulting dialog

, click Existing Worksheet, and after that

1. click G2 in the
2. Place control. Excel will insert a blank frame
3. . Using Figure A as a guide, develop this PivotTable by dragging fields to the appropriate areas. Before we continue, let’s modification the structure a bit to make the PivotTable a little bit more readable: Click inside the PivotTable and click

   the contextual Style tab. In the Layout group, select Show All Subtotals At The Bottom Of The Group from

   1. the Subtotals dropdown. From the Report Design dropdown choose Show In Tabular Type. From the Report Layout dropdown, pick Do Not Repeat Item Labels. The resulting PivotTable, shown in
   2. Figure B, is more legible than the default structure.
   3. Doing so isn’t required, but I think this format is a bit cleaner

   and easier to check out. Figure B< img src =" https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2022/08/PTMultipleTotals_BJPG-770x487.jpg" alt="" width ="770"height ="487"/ >

   Modification a

   few design settings.

   As is, the PivotTable shows subtotals for each area and a grand overall. What if you want to see the average sale for each area? In this case, you’ll require a second subtotal row.

   How to include a 2nd subtotal row to a PivotTable in Excel

   The default PivotTable is useful, but you may need more details. For example, let’s include a second subtotal row that returns the average sale for each area. You could include Total up to the Values list in the Fields pane and after that set it to typical rather of amount, but doing so will add another column of the exact same values. Honestly, the outcomes would be hectic and sidetrack viewers.

   Instead, do the following:

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

   Figure C

   Select two subtotal functions. Figure D The brand-new row shows the average sales for each area listed below the amount subtotal.

   You simply added an average row to the PivotTable, as displayed in Figure D. This process isn’t particularly user-friendly, and it’s compounded by the requirement that you select Amount and Average. With the sum subtotal row already in place, you may pick only Average, which would replace the sum row rather of placing an additional row of averages.

   Stay tuned

   Excel PivotTables are versatile. The something that’s not offered is a method to include more grand total rows. In a future post, I’ll reveal you how to do so.

   Source