Articles

Subtotals and Grand Totals in Pivot Table

Pinterest LinkedIn Tumblr

While working with a Pivot Table, you can display or hide subtotals for individual column and row fields. You can also display or hide grand totals for column/row or the entire report. In addition, you can also calculate the subtotals and grand totals with or without filtered items.

Subtotal and Grand Total Fields in a Pivot Table

Subtotal row and column fields

1. Select an item of a row or column field in the Pivot Table. Make sure it is a field and not a value.
2. Click on the PivotTable Analyze tab and click Field Settings (in the Active Field group).

PivotTable Field Settings

This displays the Field Settings dialog box as below.

Subtotals Field Settings

3. In the Field Settings dialog box, under Subtotals & Filter, select one of the following:

  • Automatic: To subtotal an outer row or column label using the default summary function. 
  • None: To remove subtotals.
  • Custom: To use a different function to Subtotal. Example Subtotal based on Average, Sum and so on. You can display one or more type of subtotal. Click Custom (if this option is available), and then select the function(s). Below shows choosing of Custom Subtotal.
Subtotals and Grand Totals in Pivot Table 1

Note: If a field contains a calculated item, you can’t change the subtotal summary function.

4. To include new items when applying a filter (in which you have selected specific items in the Filter menu), select the check box next to Include new items in the manual filter. Uncheck to exclude.

Display or hide SUBTOTAL for the entire report

  • To quickly display or hide the current subtotal, right-click the item of the field, and then select or clear the checkbox next to Subtotal “<Label name>”.

Alternatively, you can make use of the Design Tab.

  • Stay within the Pivot Table. Then from the ribbon click on the Design tab, click Subtotals (in the Layout group).
  • The following options are displayed and you can choose as per requirement:
  • Do Not Show Subtotals.
  • Show all Subtotals at Bottom of Group.
  • Show all Subtotals at Top of Group.
Subtotals and Grand Totals in Pivot Table 2

Display or hide GRAND TOTAL for the entire report

You can display or hide the grand totals for the current Pivot Table. You can also specify default settings for displaying and hiding grand totals

Display or hide grand totals

  1. Click anywhere in the Pivot Table.
  2. On the Design tab, click Grand Totals (in the Layout group,) and then select the grand total display option that you want. Below is an image showing the options.
Subtotals and Grand Totals in Pivot Table 3

Change the default behaviour for displaying or hiding grand totals

  1. Select any cell within the Pivot Table.
  2. Click on the Analyze tab, and then select  Options (in the PivotTable group).
Pivot Table Options

In the PivotTable Options dialog box, on the Totals & Filters tab, do one of the following:

PivotTable Options
  • To display grand totals by default, select either Show grand totals for columns or Show grand totals for rows, or both.
  • To hide grand totals, uncheck the box as required.

Calculate the subtotals and grand totals with or without filtered items

  1. Click anywhere in the Pivot Table.
  2. Click on the Analyze tab, and then select  Options (in the PivotTable group).

In the PivotTable Options dialog box, on the Total & Filters tab, do one of the following:

  • In case of Online Analytical Processing (OLAP) source data, select or clear the Subtotal filtered page items check box to include or exclude report filter items.
  • For non-OLAP source data, select or clear the Allow multiple filters per field checkbox to include or exclude filtered items in totals. Below is an image depicting the same.
Allow Multiple filters per field Pivot Table

Note: The OLAP data source must support the MDX expression sub select syntax.

To know more on Excel go through our Articles

Don’t miss out on free session on Excel and more. To register Click here.

Subscribe to our YouTube channel for regular updates.

Related Posts

More off this less hello salamander lied porpoise much over tightly circa horse taped so innocuously outside crud mightily…

Write A Comment