Let’s take a look. When you first add Row or Column labels to a pivot table, you won’t see any subtotals. For example, in this pivot table, let’s add Category as a row label, and Region as a column label field, and Total Sales as a value. Notice we see grand totals, but no subtotals. However, if we add a second field to the Row labels area, Product, we’ll see a subtotal for each category appear as a row in the table. As a general rule, the inner-most field in the row labels area, in this case, Product, will not be subtotaled, while outer fields, Category, in this case, will be subtotaled. The same is true of Column labels. If we add City under Region, we’ll see subtotals for each Region appear as new columns in the pivot table. You can manage the subtotals that appear in the pivot table globally or field by field. To work with subtotals all at once, navigate to the Design tab of the PivotTools ribbon, and use the Subtotals menu. You can disable all subtotals at once. Or, you can re-enable subtotals either at the top… or the bottom of the grouping field. Both commands enable all subtotals, but they each put the subtotals in a different location. Column labels are unaffected. With either option, you may want to add in blank lines after each grouping item using the Blank rows menu. You can also control subtotals individually using Field Settings. For example, to turn off subtotals for Region, go to Field Settings and change Subtotals from Automatic to None. You can do the same thing with the Category field. Access Field settings, and change Subtotals from Automatic to None. It’s important to note that the Subtotals menu on the ribbon controls the same settings. If we use that menu to enable subtotals, then check Field Settings for Category, subtotals are again set to Automatic. If we use the menu to turn off subtotals, subtotals are set to none.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
