Show All

Hide All

When working with a PivotTable report, you can display or hide subtotals for individual column and row fields, display or hide column and row grand totals for the entire report, and calculate the subtotals and grand totals with or without filtered items.

• Subtotal column and row fields

• Display or hide column and row grand totals for the entire report

• Calculate the subtotals and grand totals with or without filtered items

Subtotal row and column fields

1. Select an item of a row or column field in a PivotTable report.
2. On the Options tab, in the Active Field group, click Field Settings.

   The Field Settings dialog box is displayed.

3. Do one of the following:

   Subtotal an outer row or column label  

   1. To subtotal by the default summary function, under Subtotals, click Automatic.
   2. Optionally, to use a different function or to display more than one type of subtotal, click Custom, and then select a function.

      Functions that you can use as a subtotal

      Function	Description
      Sum	The sum of the values. This is the default function for numeric data.
      Count	The number of data values. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for data other than numbers.
      Average	The average of the values.
      Max	The largest value.
      Min	The smallest value.
      Product	The product of the values.
      Count Numbers	The number of data values that are numbers. The Count Nums summary function works the same as the COUNT worksheet function.
      StDev	An estimate of the standard deviation of a population, where the sample is a subset of the entire population.
      StDevp	The standard deviation of a population, where the population is all of the data to be summarized.
      Var	An estimate of the variance of a population, where the sample is a subset of the entire population.
      Varp	The variance of a population, where the population is all of the data to be summarized.

       Note   You cannot use a custom function with an OLAP data source.

   3. For outer row labels in compact or outline form, you can display subtotals above or below their items, or hide the subtotals, by doing the following:
      1. On the Design tab, in the Layout group, click Subtotals.
      2. Do one of the following:
         • Select Do Not Show Subtotals.
         • Select Show all Subtotals at Bottom of Group.
         • Select Show all Subtotals at Top of Group.

   Subtotal an inner row or column label  

   • To choose a function, under Subtotals, click Custom, if this option is available, and then select a function.

     Functions that you can use as a subtotal

     Function	Description
     Sum	The sum of the values. This is the default function for numeric data.
     Count	The number of data values. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for data other than numbers.
     Average	The average of the values.
     Max	The largest value.
     Min	The smallest value.
     Product	The product of the values.
     Count Nums	The number of data values that are numbers. The Count Nums summary function works the same as the COUNT worksheet function.
     StDev	An estimate of the standard deviation of a population, where the sample is a subset of the entire population.
     StDevp	The standard deviation of a population, where the population is all of the data to be summarized.
     Var	An estimate of the variance of a population, where the sample is a subset of the entire population.
     Varp	The variance of a population, where the population is all of the data to be summarized.

      Note   You cannot use a custom function with an OLAP data source.

   Remove subtotals  

   • Click None under Subtotals.

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

4. Select or clear the Include new items in manual filter check box to include or exclude new items when applying a filter in which you have selected specific items in the Filter menu.

 Tip   To quickly display or hide the current subtotal, right-click the item of the field, and then select Subtotal "<Label name>".

 Top of Page

Display or hide grand totals for the entire report

You can display or hide the totals for the current PivotTable report.

Display or hide grand totals  

1. Click the PivotTable report.
2. On the Design tab, in the Layout group, click Grand Totals, and then select one of the following:
   • Off for Rows and Columns
   • On for Rows and Columns
   • Off for Rows Only
   • On for Columns Only

Set the default behavior for displaying or hiding grand totals  

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.

   The PivotTable Options dialog box is displayed.

3. Click the Totals & Filters tab.
4. Do one of the following:

   Display grand totals  

   • Select the Show grand totals for columns check box, the Show grand totals for rows check box, or both.

   Hide grand totals  

   • Clear the Show grand totals for columns check box, the Show grand totals for rows check box, or both.

 Top of Page

Calculate the subtotals and grand totals with or without filtered items

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.

   The PivotTable Options dialog box is displayed.

3. Click the Total & Filters tab, and then do one of the following:

   OLAP source data   Do one of the following:

   • Select or clear the Subtotal filtered page items check box to include or exclude report filter items.

      Note   The OLAP data source must support the MDX expression subselect syntax.

   • Select or clear the Mark totals with * check box to display or hide an asterisk next to totals. The asterisk indicates that the visible values that are displayed and that are used when Excel calculates the total are not the only values that are used in the calculation.

      Note   This option is only available if the OLAP data source does not support the MDX expression subselect syntax.

   non-OLAP source data   Select or clear the Allow multiple filters per field check box to include or exclude filtered items in totals.

 Top of Page