Add Pivot Table Subtotals for Inner Fields

How was your weekend weather? We had a mini-blizzard yesterday, that covered the backyard with snow. But it was a good day to stay indoors, and work on Excel!

Pivot Table Subtotals

I’m confident that the weather was better last April, when I posted a video about adding multiple subtotals for a pivot table field, and you can see that video at the end of this blog post.

This week, Bob emailed a question about creating subtotals for the innermost fields, and showing subtotals for calculated fields. By default, there are no subtotals for the inner field, but you can force them to appear, by following the same steps, shown below, that you use to create multiple subtotals.

Add Custom Subtotals for Pivot Fields

To show 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

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

Custom Subtotals for Inner Fields

When you add custom subtotals for the inner fields, they appear below the last outer field, just above the grand total. So, depending on the length of your pivot table, those inner subtotals might be pretty far from the pivot items that they’re summarizing!

PivotSubtotalCustom03

Problem With Calculated Field 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 download the Pivot Table Custom Subtotals Sample workbook. The file is in Excel 2007 format, and is zipped. The file does not contain any macros.

Watch the Adding Pivot Table Subtotals for Inner Fields Video

To see the steps for adding custom subtotals to inner fields in a pivot table, you can watch this Excel video tutorial.

Or watch on YouTube: Pivot Table Custom Subtotals and Calculated Fields

Watch the Multiple Pivot Table Subtotals Video

To see the detailed steps for adding fields and creating custom subtotals, you can watch this Excel video tutorial.

Or watch on YouTube: How to Create Multiple Pivot Table Subtotals

_______________

You may also like...

5 Responses

  1. Alan says:

    Very useful tutorial.

    I am frequently asked these when training PivotTables. When I group fields by date in my sessions the subtotals for Years does not show unless you follow these steps.

  2. Gobish says:

    Thanks for the post, few quesries.Is there any way out if i require to have a subtotal for a calculated filed. Also please advice on using Names inside Pivot Calculated filed

  3. allen says:

    is there a way we can subtotal the inner field within an outter field? for example, I want to see subtotals for 1 and 2 within installation?

    it should show something like:

    Install
    1 (Central+East) 1540
    2 (Central+East) 5070

    I have a situation very similar to this and don’t know how to make it work.

    Thanks in advance.

  4. Ron says:

    I was able to get sub-totals to appear for calculated fields by right-clicking the outer field, unchecking the sub-total option, then right-clicking the outer field and clicking on the sub-total option again to re-display the sub-totals.

Leave a Reply to Ron Cancel reply

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