Pivot Table Custom Subtotals in Excel

Pivot Table Custom Subtotals

In a pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. The innermost field doesn’t show subtotals, but you can force them to appear, by creating pivot table custom subtotals. Learn more about pivot table subtotals on my Contextures website.

Add Custom Subtotals for Pivot Fields

To show pivot table custom subtotals for the inner or outer pivot fields, follow these steps:

  1. Right-click on an item in the pivot field that you want to change. For example, right click on a region name cell, in the Region field
  2. In the popup menu, click Field Settings
  3. In the Field Settings dialog box, on the Subtotals & Filters tab, click Custom
  4. Click on one or more summary function in the list of functions, then click OK

PivotSubtotalCustom01

See the Custom Subtotals for Outer Fields

When you add custom subtotals for the outer fields, they appear below the pivot field items, even if you have selected the option to “Display All Subtotals at Top of Group.”

You can’t change the order of the custom subtotals – they’ll appear in the same order as in the list of functions.

PivotSubtotalCustom02

See the Inner Field Subtotals

To create subtotals for the inner fields, you create custom subtotals, following the steps above. However, it is important to understand that when you add custom subtotals for the inner fields, they appear below the last outer field, just above the grand total.

The following screen shot shows an example. City is the inner field, and the counts for the cities are just above the grand total.

PivotSubtotalCustom03

To avoid confusing people who are using your pivot table, add a note on the worksheet, to explain that the city subtotals are at the end of the pivot table.

You can also format the outer field to have a blank row after each item. In the next screen shot, the Field Settings for the Region field are being changed. On the Layout & Print tab, a check mark has been added to “Insert blank line after each item label”

pivotsubtotal08

That creates a blank line after the West region, which makes it easier to see where the inner field subtotals begin.

Pivot Table Custom Subtotals

Problem With Calculated Field Custom Subtotals

If you have calculated fields in your pivot table, they will not show any custom subtotals. In the screen shot below, the Tax column is a calculated field, and its subtotals are blank. Stick with automatic subtotals, if you need to see subtotals for your calculated fields.

PivotSubtotalCustom02

Download the Sample Workbook

To experiment with the pivot table subtotals shown in this example, you can go to the pivot table subtotals page on my Contextures website, and download the Custom Subtotals Sample workbook. The file is in xlsx format, and is zipped. The file does not contain any macros.

Video: Pivot Table Custom Subtotals

Watch this short video, to see how to add custom subtotals. It also shows the missing subtotals for a calculated field, and shows the placement of the inner field subtotals.


______________________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.