Search
Contextures Sites
| |||
|
Time-saving
|
|
|||
Contextures Inc., Copyright ©2013
All rights reserved.
Search Contextures Sites
Time-saving
Pivot Table add-in
Excel Pivot Table Subtotals
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
Change the Subtotal Summary Function
Show Multiple Subtotals
Pivot Table Tutorial ListView the steps in a short Pivot Table Subtotals video
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.
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.
- 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.
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.
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.
If you don't want to see the subtotals in a pivot table, you can follow these steps to turn them off.
- 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
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.
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.
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.
The 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 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.
Change the Subtotal Summary Function
Instead of using the Automatic setting for subtotals, you can select a Custom setting. To change the setting:
- 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.
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 a short Pivot Table Subtotals video.
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: November 15, 2009 2:24 PM
