Excel Pivot Table Custom Subtotal Limitations

In an Excel pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. You can also add extra subtotals, if needed, by creating custom subtotals for a pivot field. There are some limitations though, which you can see in the video and notes below.

Video: Pivot Table Custom Subtotals

The benefit of using pivot table custom subtotals is that you can show additional totals, such as Count or Average, without adding more columns to your pivot table.

In this short video, you can see the steps for adding custom subtotals in a pivot table. You’ll also see a couple of custom subtotal limitations.

There are written steps below the video, and there’s more information about pivot table subtotals on my Contextures website.

Add Custom Subtotals for Pivot Fields

To show pivot table custom subtotals for a pivot field, follow these steps:

Right-click on an item in the pivot field that you want to change.
- In the screen shot below, I right-clicked on the East cell, in the Region field
In the popup menu that appears, click the Field Settings command
In the Field Settings dialog box, go to the Subtotals & Filters tab
Under the Subtotals heading, click the Custom radio button
Next, click on one or more of the summary function names in the list of functions
- You don’t need to press the Ctrl key to select multiple functions
- If you change your mind, click on a selected function, to “unselect” it
- I selected two functions – Sum and Average
- Tip: For details on how those functions work, go to the Pivot Table Summary Functions page on my Contextures site
When you’re finished selecting function, click OK, to close the dialog box and add those subtotals

Custom Subtotals for Region Field

In the screen shot below, the Sum and Average custom subtotals have been added for the Region field, in the quantity column (Qty).

Now, in a single cell, I can see that the average quantity in that region was 53. That’s a real space saver!

Note: Because the pivot table report layout is in compact form, the column heading says “Row Labels”, instead of showing a field name

Custom Subtotal Limitations

Custom subtotals can be helpful, but here are a couple of layout limitations to keep in mind, before you start using them.

Also, see the next section for one more limitation.

You can’t change the order of the custom subtotals
- Functions are listed in the same order as in the Field Settings list of summary functions, where you selected them.
Custom subtotals for the outer fields will always appear at bottom of group, below the pivot field items, even if you have selected the option to show subtotals at the top of the group
- In the screen shot below, the Region field is set to “Display subtotals at the top of each group.”
- However, the custom row subtotals ignore that setting, and are listed below each region

NOTE: The setting for “Display subtotals at the top of each group” is only available for pivot tables in Compact Form or Outline Form (like the pivot table shown below)

Limitation With Calculated Field Custom Subtotals

In the screen shot below, you can see a 3rd limitation with pivot table custom subtotals:

If you have calculated fields in your pivot table, those fields will not show any custom subtotals.

In the screen shot below, the Tax column is a calculated field, and its custom subtotals cells are blank.

So, if you need to see subtotals for your calculated fields, stick with the automatic subtotals, and avoid custom subtotals.

Get 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 zipped Excel file is in xlsx format, and does not contain any macros.

Video: Pivot Table Multiple Subtotals

Here’s another short video, where you can to see the steps for adding single or multiple custom subtotals in a pivot table.

There are written steps on the pivot table subtotals page on my Contextures website.

______________________