Show or hide subtotals in a pivot table, for all fields, or individual pivot fields. Show multiple subtotals, with data summarized with different functions
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.
When you add another field to the Row Labels area, a subtotal is automatically created for the first field. In this example, the District field is added to the Row Labels area, below the Service field.
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.
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.
If you don't want to see the subtotals in a pivot table, you can follow these steps to turn them off.
All the subtotals in the pivot table -- for both rows and columns -- will be hidden.
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.
The subtotal for that field will be hidden, and other subtotals will not be affected.
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.
You canmove the subtota ls to the bottom of the group, if you prefer. To move the subtotals, follow these steps.
The position of the subtotals is also affected by the Report Layout applied to the pivot table.
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.
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.
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.
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.)
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.
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.
In this short Excel video tutorial, you can see how to create custom subtotals, and see the missing custom subtotals for the calculated fields.
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. For example, in the screen shot below,
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.
The subtotal number format will now match the other dates in the field.
View the steps in a short Subtotal Date Format video.
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Last updated: November 15, 2017 11:04 AM
Contextures RSS Feed