Change Summary Function for Pivot Table Subtotal

When you add a field to the Values area of an Excel Pivot Table, it automatically shows the Sum or Count for all the items in that field. Here we can see the total labor cost for each Service Type.

If you add more than one field to the Row Labels or Column Labels area of the Pivot Table, a subtotal is automatically created for each field except the last one (the Inner Field). The subtotal, by default, uses the same summary function as the Value field.

In our example, the District field has been added to the Row Labels area. Service Type is now an Outer Field, and has a subtotal for each service. District is the Inner Field, and does not have subtotals.

Change the Subtotal Summary Function

Instead of using the default summary function for subtotals, you can select a different function. To change the setting:

1. Right-click a label for the field in which you want to change the subtotal. In this example, right-click cell B3, which has the Install label.
2. In the pop-up menu, click Field Settings
3. In the Field Settings dialog box, click the Subtotals & Filters tab
4. Under Subtotals, click Custom
5. In the list of Summary Functions, click one or more function names
6. Click OK to close the dialog box.

Subtotals on Inner Fields

For the Inner Field in the Row Labels or Column Labels area, the default subtotals are not displayed. So, in the pivot table shown above, there’s no subtotal for the District field.

However, if you create Custom Subtotals for an Inner Field, those subtotals appear at the end of the Pivot Table. In this example, Custom subtotals for Count and Max were created for the District field. You can see the District subtotals at the bottom of the Pivot Table, just above the Grand Total.

More Info on Pivot Table Subtotals

You can read more about pivot table subtotals, and the steps for changing them, on the Contextures website: Excel Pivot Table Subtotals.

Watch the Pivot Table Subtotals Video

To see the steps for changing the pivot table subtotals, and creating multiple subtotals, you can watch this short video.

___________