Home > Pivot Tables > Calculation > Subtotals

Excel Pivot Table Subtotals

Show or hide subtotals in a pivot table, for all fields, or individual pivot fields. Show multiple subtotals, with data summarized with different functions

no custom subtotals for calculated fields

Creating Pivot Table Subtotals

Add Another Subtotal

Hide All Subtotals

Hide or Show One Subtotal

Show Subtotals at Top or Bottom

Video: Show and Move Subtotals

Automatic Subtotal Summary Function

Create a Custom Subtotal

Show Multiple Subtotals

Subtotals for Calculated Fields

Subtotal Date Formatting

Get the Sample File

Creating Pivot Table Subtotals

If your pivot table has only one field in the Row Labels area, you won't see any Row subtotals.

In the pivot table shown below, Service is in the Row Labels area, Lead Tech is in the Column Labels area, and Labor Cost is in the Values area.

Because Service is the only field in the Row Labels area, it has no subtotal.

no subtotal

Multiple Row Fields

When you add another field to the Row Labels area, a subtotal is automatically created for the first field.

In the next screen shot, the District field has been added to the Row Labels area, below the Service field.

The Service field is an Outer Field, because there is a field below it (District).
The District field is an Inner Field, because there is NO field below it.

Because Service is now an Outer Field, it automatically has a subtotal after each Service type. Each subtotal shows the name of the Service type, and "Total", such as Install Total.

outer field has subtotal

Add Another Subtotal

If you add another field to the Row Labels area, below the District field, the new field becomes the Inner Field, and District changes to an Outer Field.

In the pivot table below, the Technician Count field was added below District, and the District field now has a subtotal after each District name.

subtotal after each district name

Hide All Subtotals

In a new pivot table, when you add multiple fields to the Row Labels or column Labels areas, subtotals are automatically shown for the outer fields.

In the screen shot below, there are two fields in the Row Labels area, and subtotals are shown at the top.

pivot table subtotals at top

If you don't want to see the subtotals in a pivot table, you can follow these steps to remove subtotals from the entire report.

Select any cell in the pivot table.
On the Excel Ribbon, under PivotTable Tools, click the Design tab.
In the Layout group, click Subtotals
Click Do Not Show Subtotals

do not show subtotals

All the subtotals in the pivot table -- for both rows and columns -- will be hidden.

Hide or Show One Subtotal

Instead of hiding all the subtotals, you can turn subtotals on or off for a specific pivot field.

To hide the subtotal for a specific field, follow these steps.

Right-click one of the labels in the pivot field where you want to hide subtotals.
In the pop-up menu, click on the Subtotal command, to remove the check mark.

subtotal hide field

The subtotal for that field will be hidden, and other subtotals will not be affected.

Show Subtotals at Top or Bottom

In a new pivot table, when you add fields to the Row Labels area, subtotals are automatically shown at the top of each group of items, for the outer fields.

pivot table subtotals at top

You canmove the subtota ls to the bottom of the group, if you prefer. To move the subtotals, follow these steps.

Select a cell in the pivot table, and on the Ribbon, click the Design tab.
In the Layout group, click Subtotals, and then click Show All Subtotals at Bottom of Group.

Notes:

Column fields are not affected by the setting for Subtotals. Column subtotals always appear at the bottom of the group.
The labels for the outer Row fields are always above the labels for the related inner fields, even when the subtotals are at the bottom of the group.

Report Layout Effect

The position of the subtotals is also affected by the Report Layout applied to the pivot table.

In Compact Form and Outline Form, you can show the subtotals at either the top or bottom of the group.
In Tabular Form, the labels for the outer Row fields are on the same row as the first label for the related inner fields, and the subtotals can only be shown at the bottom of the group

Video: Show and Move Subtotals

Watch this video to see how to move the subtotals to the top or bottom of a group, and see how the report layout affects the subtotal position.

Automatic Subtotal Summary Function

When a subtotal is added to a pivot table, its Summary Function is set to Automatic. With that setting, the subtotal automatically uses the same Summary Function as the Value fields in each column.

In the pivot table shown above, the Value fields are using the SUM function, so the subtotals also show the SUM of the values.

In the pivot table shown below, the Value fields have been changed to the MAX function, so the subtotals also show the MAX of the values. A few of the MAX values are highlighted in green, to show that the values and both subtotals are the same.

Even though the Summary Function has been changed to MAX, each subtotal still shows the name of the item, and "Total", such as Central Total.

subtotal label changed

Create a Custom Subtotal

Instead of using the Automatic setting for subtotals, you can create a custom subtotal in a pivot table -- either a single custom subtotal, or multiple custom subtotals. Watch this short video to see the steps, and the written instructions are below the video.

To create a custom subtotal:

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

Note: The subtotals will be calculated on the source data, not on the values showing in the pivot table. For example, if a field uses the MAX summary function, and the subtotal shows the AVERAGE, it will be an average from the values in the source data, not an average of the MAX values. (To calculate the Average of the Max values, you could use formulas outside of the pivot table, or create a new pivot table, based on the original one.)

field settings

Show Multiple Subtotals

In the Field Settings dialog box shown above, there are two functions, Count and Max, selected in the list of Summary Functions for the Service field.

After selecting these functions, the pivot table shows two subtotals for each Service type. When you use Custom functions, the subtotal row shows the item name, and the name of the Function, such as Install Count.

The subtotals for District are not changed, nor are the other values in the pivot table.

View the steps in the short Pivot Table Subtotals video, in the previous section.

show multiple subtotals

Subtotals for Calculated Fields

Automatic subtotals will appear for normal fields and calculated fields in a pivot table. However, custom subtotals will not appear for calculated fields. The custom subtotals will be blank for any calcuated fields in the pivot table layout.

no custom subtotals for calculated fields

Video: Subtotals for Calculated Fields

In this short Excel video, you can see how to create custom subtotals, and see the missing custom subtotals for the calculated fields.

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

✅ Click here for Transcript

Video Transcript: Pivot Table Custom Subtotals and Calculated Fields

Here is the full transcript for the Pivot Table Custom Subtotals and Calculated Fields video.

In this pivot table, we're showing the sum of quantity sold in each region and within the cities in that region.

If we wanted to see a count of the orders, we could add the quantity field again, and instead of sum, show account, and that would create another column here.

Another way of showing the count though, is to use a subtotal.
Right now, we have a subtotal for each region showing the total sum for the East and the West.

We can change that, so instead of just one subtotal, there are two or more subtotals.

To do that, I'm going to right-click on one of the region names and click Field Settings.

In the Field Settings dialogue box, on the subtotals and filters tab,
we can see that subtotals is set for automatic.

I'll select Custom, and then I can click on one or more of these built-in summary functions.

So if I want to continue to see the sum, I'll click Sum.

And I'd like the count as well so I'll use Count and then click OK.

Now, instead of that default subtotal that we had, we now have the sum and the count for each region.

You'll notice though, that only applies to the quantity column.

We also have a calculated field here, which is taking the total amount of dollars of that sale, and multiplying it by 0.05.

So for that calculated field, we don't get these custom subtotals.

So if you're using calculated fields, you might want to continue using automatic subtotals., instead of these custom ones.

The inner fields, here we have region is the outer field and city is the inner field and they don't automatically get any kind of subtotal.

So if I want to see subtotals for those, I can right-click one of the city names, go to Field Settings, and again, in this Field Settings dialogue box (it's set for automatic), I'll click Custom, and then I could click Sum or Count.

Now I can already see the total sum for each city, so I'll just select Count here and click OK.

All of the subtotals for those cities appear after the last region.
So below the second region that we have, you can see all the city counts and then finally the grand total.

So you can't rearrange these, they're just in the order that they appear In the pivot table and they come before the grand total.

So you can't change that, but at least you can see the count for each city without adding another column to your pivot table.

Subtotal Date Formatting

If you show subtotals for a date field, the dates might be in a format that is different from the other dates in that field.

Watch this short video to see why this happens, and how to avoid the problem of different formats.

You can get the sample file for this video in the Download section, below.

Different Date Format in Subtotals

There's an example of different date formats for subtotals, in the screen shot below,

Order date (in row 4) is formatted as d-mmm-yy
Subtotal date (in row 8) is formatted as mmm/yy

subtotal with different date format

Fix Different Date Format

This problem occurs if you select the date cells and format them, and then add subtotals.

To prevent the problem, change the Number Format of the date field, by following these steps.

Right-click one of the date field labels, and in the popup menu, click Field Settings.
Click the Number Format button
Select a Date format
Click OK, twice, to close the dialog boxes

The subtotal number format will now match the other dates in the field.

View the steps in a short Subtotal Date Format video.

subtotal with same date format

Get the Sample Files

1) Click here to download the sample workbook for the Subtotal Date Format tutorial. The zipped Excel file is in xlsx format, and does not contain any macros.

2) Click here to download the sample file for Pivot Table Custom Subtotals. The zipped Excel file is in xlsx format, and does not contain any macros.

Pivot Table Introduction

Grouping Data

Running Totals