MS-Excel / Excel 2003

Selecting new summary functions

By default, Excel uses the SUM function to total the values in the numeric field(s) that you assign as the Data Items in the pivot table. Some data summaries require the use of another summary function, such as the AVERAGE or COUNT function. To change the summary function that Excel uses, open the Field dialog box for one of the Data Items fields (doubleclick the field's label at the intersection of the first Column Field label and the Row Field label).

After you open the Field dialog box, you can change its summary function from the default SUM to any of the following functions by selecting it in the Summarize By list box:

  • COUNT: Displays the count of the records for a particular category. (Note that COUNT is the default setting for any text fields that you use as Data Items in a pivot table.)
  • AVERAGE: Calculates the average (that is, the arithmetic mean) for the values in the field for the current category and page filter.
  • MAX: Displays the largest numeric value in that field for the current category and page filter.
  • MIN: Displays the smallest numeric value in that field for the current category and page filter.
  • PRODUCT: Displays the product of the numeric values in that field for the current category and page filter. (All non-numeric entries are ignored.)
  • COUNT NUMS: Displays the number of numeric values in that field for the current category and page filter. (All non-numeric entries are ignored.)
  • STDDEV: Displays the standard deviation for the sample in that field for the current category and page filter.
  • STDDEVP: Displays the standard deviation for the population in that field for the current category and page filter.
  • VAR: Displays the variance for the sample in that field for the current category and page filter.
  • VARP: Displays the variance for the population in that field for the current category and page filter.

After you select the new summary function, click OK to have Excel apply the new function to the data presented in the body of the pivot table.

[Previous] [Contents] [Next]

In this tutorial:

  1. Summarizing Data with Pivot Tables and Pivot Charts
  2. Excel 2003 Creating Pivot Tables
  3. Pivoting the fields in the table
  4. Formatting the values in the pivot table
  5. Selecting new summary functions
  6. Creating a calculated field for the pivot table
  7. Creating a Pivot Chart