Suppress Subtotals in Your Excel Pivot Table Reports

By Michael Alexander

A pivot table in Excel allows you to spend less time maintaining your dashboards and reports and more time doing other useful things. Notice that each time you add a field to your pivot table, Excel adds a subtotal for that field. There may be times however, when the inclusion of subtotals either doesn’t make sense or just hinders a clear view of your pivot table report.

For example, this figure shows a pivot table in which the subtotals inundate the report with totals that hide the real data you’re trying to report.

image0.jpg

Remove all pivot table subtotals at one time

You can remove all subtotals at once by taking these actions:

  1. Click anywhere inside your pivot table to activate the PivotTable Tools context tab on the Ribbon.

  2. Select the Design tab on the Ribbon.

  3. Click the Subtotals icon and select Do Not Show Subtotals, as shown in this figure.

    image1.jpg

As you can see in this figure, the same report without subtotals is much more pleasant to review.

image2.jpg

Remove the subtotals for only one field

Maybe you want to remove the subtotals for only one field? In such a case, you can take the following actions:

  1. Right-click any value within the target field.

  2. Select Field Settings.

    The Field Settings dialog box appears.

  3. Choose the None option under Subtotals, as demonstrated in this figure.

    image3.jpg

  4. Click OK to apply the changes.

Removing pivot table grand totals

There may be instances when you want to remove the grand totals from your pivot table.

  1. Right-click anywhere on your pivot table.

  2. Select PivotTable Options.

    The PivotTable Options dialog box appears.

  3. Click the Totals & Filters tab.

  4. Click the Show Grand Totals for Rows check box to deselect it.

  5. Click the Show Grand Totals for Columns check box to deselect it.