Prevent Subtotals from Appearing

If you add more than one field to the Rows or Columns area in a pivot table, some of the fields automatically get subtotals. In the screen shot below:

• the City field has a subtotal, because it is an outer field – there is at least one field after it in the layout. Outer fields get subtotals by default.
• the Category field does not have a subtotal, because it is an inner field – there is no field after it in the layout. The innermost field in the Rows or Columns area doesn’t get a subtotal.

Turn Off Automatic Subtotals

You can change a setting in the pivot table, to remove all the subtotals, and to stop new subtotals from automatically appearing.

• Select a cell in the pivot table, and on the Ribbon, click the Design tab.
• In the Layout group, click Subtotals, and then click Do Not Show Subtotals.

NOTE: This change turns off the subtotals for all fields in the selected pivot table.

Turn On Automatic Subtotals

If you’d like to turn automatic subtotals on again, 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 or Show all Subtotals at Top of Group

NOTE: If the pivot table is in Tabular layout, subtotals can only appear at the bottom of the group.

Turn Field Subtotals On or Off

Whether the pivot table subtotals are turned on or off, you can also turn subtotals on or off for an individual field. In this example, automatic subtotals are on, and subtotals will be turned off for the Category field only.

• Right-click a row label in the Category field
• In the context menu, click Subtotal “Category”.

Using this command toggles the Subtotal setting – if the Subtotal setting was on, it is turned off.

NOTE: The subtotal for the selected field appears in the position that was last specified for the pivot table. For example, if subtotals were at the top of the group, and then you turned off subtotals, the field’s subtotals appear at the top of its group.

Set Subtotal Defaults

If you have a copy of my PivotPower Premium add-in, you can make “Hide All Subtotals” one of your default settings. Then, select a pivot table, and click Apply Defaults, and your Subtotals setting will be applied, along with all your other defaults.

__________________________