Clean Up Pivot Table Subtotals

When there are multiple row fields in a pivot table, the outer fields automatically show subtotals. The pivot table layout can look cluttered if there are too many subtotals, especially if they are close together. Here are a couple of tips to help you clean up pivot table subtotals, to make the data easier to read.

Cluttered Column

For example, this pivot table is in Tabular layout, with subtotals showing for the District and Category fields, at the bottom of each group.

Column C looks cluttered, because the subtotals are close together, and it’s hard to see the category names.

Clear the Clutter

To remove the subtotal labels for the Category column, follow these steps:

• Select one of the Category subtotal labels, such as Cookies Total, in cell C5
• Press the Spacebar on your keyboard, to replace the label with a space character
• Press Enter

All the Category  subtotal labels disappear, and the pivot table is much easier to read. The subtotal numbers are not affected, and they appear at the bottom of each category.

Format Subtotal Labels

Another quick way to make a pivot table look less cluttered is by formatting the subtotal labels. By default, the subtotals have the same formatting as the item name, so change the format, to give less impact to the subtotals.

First, to select the subtotal labels for the Category column, follow these steps:

• Point to the left side of a cell that contains a Category subtotal labels, such as Cookies Total, in cell C5
• When the pointer changes to a black arrow, click to select all the subtotal cells

• Then, on the Excel Ribbon, click the Analyze tab, under the PivotTable Tools heading
• In the Actions group, click Select, then click Labels

Now the subtotal labels are selected, but the sub total values are not selected.

NOTE: If you have trouble selecting parts of the pivot table, see the Pivot Table Selections page on my website for a video and written details.

Format the Subtotal Labels

With the subtotal labels selected, you can change the formatting, to make them less prominent in the pivot table.

For example, change the font colour to a medium grey, and indent the text, to move it to the right.

With those changes, the subtotal labels are still visible, if anyone wants to read them, but they fade into the background, and reduces the cluttered look.

Move Subtotals to the Top

Another option for pivot table subtotals is to show them at the top of the group, instead of the bottom.

• If your pivot table is in Compact or Outline layout, you can show subtotals at the top or bottom.
• If your pivot table is in Tabular layout, subtotals always appear at the bottom

To show subtotals at the top of each group:

• If your pivot table is in Tabular layout, change to Outline or Compact layout
• With any cell in the pivot table selected, click the Design tab on the Excel Ribbon, under the Pivot Table Tools tab
• Click the Subtotals command, then click Show All Subtotals at Top of Group

With that setting, there are no additional labels for the subtotals – just the Item name appears.

More Pivot Table Subtotals Info

For more information, go to the Pivot Table Subtotals page on my Contextures website. There are videos, written instructions, and sample files to download.

____________________________

Clean Up Pivot Table Subtotals

 

_____________________________