Turn Off Pivot Table Subtotals

When you add field to the Row or Column area of a pivot table, subtotals automatically appear in the outer fields. For example, in the following pivot table, Region and Employee are the outer fields, and show subtotals.

Remove Subtotals in Excel 2007

In Excel 2007 you can turn off all subtotals for the active pivot table, and prevent new subtotals from appearing:

  1. Select a cell in the pivot table
  2. On the Ribbon, click the Design tab
  3. In the Layout group, click Subtotals, and click Do Not Show Subtotals.

However, there’s no setting you can change to make this the default behaviour for all pivot tables.

Remove Subtotals in Excel 2003

In earlier versions of Excel, you have to turn off the subtotal for each field individually, either manually or programmatically.
To manually turn off a field’s subtotals:

  1. Double-click the field button, to open the PivotTable Field dialog box.
  2. Under Subtotals, click None, then click OK.


Programmatically Remove Subtotals

To make it easier to remove all the subtotals in Excel 2003, there’s a subtotals command in my Pivot Table Add-in.