How to Modify the Pivot Table’s Summary Function in Excel 2013

By default, Excel 2013 uses the good old SUM function to create subtotals and grand totals for the numeric field(s) that you assign as the Data Items in the pivot table.

Some pivot tables, however, require the use of another summary function, such as AVERAGE or COUNT. To change the summary function that Excel uses, click the Sum Of field label that’s located at the cell intersection of the first column field and row field in a pivot table. Next, click the Field Settings command button on the Analyze tab to open the Value Field Settings dialog box for that field.

image0.jpg

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

  • Count to show the count of the records for a particular category (Count is the default setting for any text fields that you use as Data Items in a pivot table)

  • Average to calculate the average (that is, the arithmetic mean) for the values in the field for the current category and page filter

  • Max to display the largest numeric value in that field for the current category and page filter

  • Min to display the smallest numeric value in that field for the current category and page filter

  • Product to display the product of the numeric values in that field for the current category and page filter (all non-numeric entries are ignored)

  • Count Numbers to display the number of numeric values in that field for the current category and page filter (all non-numeric entries are ignored)

  • StdDev to display the standard deviation for the sample in that field for the current category and page filter

  • StdDevp to display the standard deviation for the population in that field for the current category and page filter

  • Var to display the variance for the sample in that field for the current category and page filter

  • Varp to display the variance for the population in that field for the current category and page filter

After you select the new summary function to use in the Summarize Value Field By list box on the Summarize Values By tab of the Value Field Settings dialog box, click OK to have Excel apply the new function to the data present in the body of the pivot table.

blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.