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 (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.). Optionally, you can also create a custom calculation (custom calculation: A method of summarizing values in the data area of a PivotTable report by using the values in other cells in the data area. Use the Show data as list on the PivotTable Field dialog for a data field to create custom calculations.).

  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.

  1. Click the Summarize by tab.
  2. In the Summarize value field by box, click the summary function that you want to use.

ShowSummary 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 (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.), such as OLAP data and for calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) and fields with calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.), you can't change the summary function.

  1. 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.

ShowCustom calculations that you can use

Function Result
Normal Turns off custom calculation.
Difference From Displays a value as the difference from the value of the Base item in the Base field.
% Of Displays a value as a percentage of the value of the Base item in the Base field.
% Difference From Displays a value as the percentage difference from the value of the Base item in the Base field.
Running Total in Displays the value for successive items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) in the Base field as a running total.
% Of Row Displays the value in each row or category as a percentage of the total for the row or category.
% Of Column Displays all the values in each column or series as a percentage of the total for the column or series.
% Of Total Displays a value as a percentage of the grand total of all the values or data points in the report.
Index

Calculates a value as follows:

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

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

Base field should not be the same field that you chose in step 1.

  1. 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.
  2. 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.

 
 
Applies to:
Excel 2007