Change the summary function or custom calculation for a field in a PivotTable report

Data in the Values area summarize the underlying source data (not the value that is showing) in the PivotChart report in the following way: numeric values use the SUM function and text values use the COUNT function. However, you can change the summary function. Optionally, you can also create a custom calculation.

  1. Select a field in the Values area for which you want to change the summary function of the PivotTable report.

  2. On the Analyze tab, in the Active Field group, click Active Field, and then click Field Settings.

    The Value Field Settings dialog box is displayed.

    The Source Name is the name of the field in the data source.

    The Custom Name displays the current name in the PivotTable report, or the source name if there is no custom name. To change the Custom Name, click the text in the box and edit the name.

  3. Click the Summarize Values By tab.

  4. In the Summarize value field by box, click the summary function that you want to use.

    Summary functions you can use

    Function

    Summarizes

    Sum

    The sum of the values. This is the default function for numeric values.

    Count

    The number of values. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for values 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 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 values 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 values to be summarized.

    Note: For some types of source data, such as OLAP data and for calculated fields and fields with calculated items, you can't change the summary function.

  5. Optionally, you can use a custom calculation by doing the following:

    1. Click the Show Values As tab.

    2. Click the calculation that you want in the Show values as box.

      Use this calculation

      To

      No calculation

      Turn off custom calculation.

      % of Grand Total

      Display a value as a percentage of the grand total of all the values or data points in the report.

      % of Column Total

      Display all the values in each column or series as a percentage of the total for the column or series.

      % of Row Total

      Display the value in each row or category as a percentage of the total for the row or category.

      % Of

      Display a value as a percentage of the value of the Base item in the Base field.

      % of Parent Row Total

      Calculate (value for the item) / (value for the parent item on rows).

      % of Parent Column Total

      Calculate (value for the item) / (value for the parent item on columns).

      % of Parent Total

      Calculate(value for the item) / (value for the parent item of the selected Base field).

      Difference From

      Display a value as the difference from the value of the Base item in the Base field.

      % Difference From

      Display a value as the percentage difference from the value of the Base item in the Base field.

      Running Total in

      Display the value for successive items in the Base field as a running total.

      % Running Total in

      Display the value as a percentage for successive items in the Base field as a running total.

      Rank smallest to largest

      Display the rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value with a higher rank value.

      Rank largest to smallest

      Display the rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller value with a higher rank value.

      Index

      Calculate a value as follows:

      ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

    3. Select a Base field and Base item, if these options are available for the calculation that you chose.

      Note: The base field should not be the same field that you chose in step 1.

  6. To change the way that numbers are formatted, click Number Format, and in the Number tab of the Format Cells dialog box, select a number format, and then click OK.

  7. If the report has multiple value fields, repeat the previous steps for each one that you want to change.

Note: To use more than one summary function for the same field, add the field again from the PivotTable Field List, and then repeat the steps by choosing the other function that you want.

  1. Select a field in the Values area for which you want to change the summary function of the PivotTable report.

  2. On the Options tab, in the Active Field group, click Active Field, and then click Field Settings.

    The Value Field Settings dialog box is displayed.

    The Source Name is the name of the field in the data source.

    The Custom Name displays the current name in the PivotTable report, or the source name if there is no custom name. To change the Custom Name, click the text in the box and edit the name.

  3. Click the Summarize Values By tab.

  4. In the Summarize value field by box, click the summary function that you want to use.

    Function

    Summarizes

    Sum

    The sum of the values. This is the default function for numeric values.

    Count

    The number of values. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for values 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 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 values 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 values to be summarized.

    Note: For some types of source data, such as OLAP data and for calculated fields and fields with calculated items, you can't change the summary function.

  5. Optionally, you can use a custom calculation by doing the following:

    1. Click the Show Values As tab.

    2. Click the calculation that you want in the Show values as box.

      Use this calculation

      To

      No calculation

      Turn off custom calculation.

      % of Grand Total

      Display a value as a percentage of the grand total of all the values or data points in the report.

      % of Column Total

      Display all the values in each column or series as a percentage of the total for the column or series.

      % of Row Total

      Display the value in each row or category as a percentage of the total for the row or category.

      % Of

      Display a value as a percentage of the value of the Base item in the Base field.

      % of Parent Row Total

      Calculate (value for the item) / (value for the parent item on rows).

      % of Parent Column Total

      Calculate (value for the item) / (value for the parent item on columns).

      % of Parent Total

      Calculate(value for the item) / (value for the parent item of the selected Base field).

      Difference From

      Display a value as the difference from the value of the Base item in the Base field.

      % Difference From

      Display a value as the percentage difference from the value of the Base item in the Base field.

      Running Total in

      Display the value for successive items in the Base field as a running total.

      % Running Total in

      Display the value as a percentage for successive items in the Base field as a running total.

      Rank smallest to largest

      Display the rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value with a higher rank value.

      Rank largest to smallest

      Display the rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller value with a higher rank value.

      Index

      Calculate a value as follows:

      ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

    3. Select a Base field and Base item, if these options are available for the calculation that you chose.

      Note: The base field should not be the same field that you chose in step 1.

  6. To change the way that numbers are formatted, click Number Format, and in the Number tab of the Format Cells dialog box, select a number format, and then click OK.

  7. If the report has multiple value fields, repeat the previous steps for each one that you want to change.

Note: To use more than one summary function for the same field, add the field again from the PivotTable Field List, and then repeat the steps by choosing the other function that you want.

  1. Select a field in the Values area for which you want to change the summary function of the PivotTable report.

  2. On the Options tab, in the Active Field group, click Active Field, and then click Field Settings.

    The Value Field Settings dialog box is displayed.

    The Source Name is the name of the field in the data source.

    The Custom Name displays the current name in the PivotTable report, or the source name if there is no custom name. To change the Custom Name, click the text in the box and edit the name.

  3. Click the Summarize by tab.

  4. In the Summarize value field by box, click the summary function that you want to use.

    Summary functions you can use

    Function

    Summarizes

    Sum

    The sum of the values. This is the default function for numeric values.

    Count

    The number of values. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for values 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 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 values 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 values to be summarized.

    Note: For some types of source data, such as OLAP data and for calculated fields and fields with calculated items, you can't change the summary function.

  5. Optionally, you can use a custom calculation by doing the following:

    1. Click the Show values As tab.

    2. Click the calculation that you want in the Show values as box.

      Use this function

      To

      Normal

      Turn off custom calculation.

      Difference From

      Display a value as the difference from the value of the Base item in the Base field.

      % Of

      Display a value as a percentage of the value of the Base item in the Base field.

      % Difference From

      Display a value as the percentage difference from the value of the Base item in the Base field.

      Running Total in

      Display the value for successive items in the Base field as a running total.

      % Of Row

      Display the value in each row or category as a percentage of the total for the row or category.

      % Of Column

      Display all the values in each column or series as a percentage of the total for the column or series.

      % Of Total

      Display a value as a percentage of the grand total of all the values or data points in the report.

      Index

      Calculate a value as follows:

      ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

    3. Select a Base field and Base item, if these options are available for the calculation that you chose.

      Note: The base field should not be the same field that you chose in step 1.

  6. To change the way that numbers are formatted, click Number Format, and in the Number tab of the Format Cells dialog box, select a number format, and then click OK.

  7. If the report has multiple value fields, repeat the previous steps for each one that you want to change.

Note: To use more than one summary function for the same field, add the field again from the PivotTable Field List, and then repeat the steps by choosing the other function that you want.

See Also

Summary functions available in PivotTable reports

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×