Search Contextures Sites

Excel Pivot Table Summary Functions

Change the Summary Function
Sum Function
Sum Function Macro
Count Function
Average Function
Max Function
Min Function
Product Function
Count Numbers Function
StDev and StDevP Functions
Var and Varp Functions
Download the Sample File

Pivot Table Tutorials and Videos

When you add a field to the pivot table's Values area, 11 different functions, such as Sum, Count and Average, are available to summarize the data.

The summary functions in a pivot table are similar to the worksheet functions with the same names, with a few differences as noted in the descriptions that follow.

pivot table summary functions

Change the Summary Function

When you add a numerical field to the pivot table's Values area, Sum or Count will be the default summary function. To select a different summary function, follow these steps:

  1. Right-click on a cell in the Value field that you want to change.
  2. In the pop-up menu, click Summarize Values By
  3. Click on the Summary Function that you want to use

pivot table summary function select

To change many fields at once, you can use a macro. Or, if you've bought a copy of my PivotPower Premium add-in, just click Sum All, or select a different function in the Summarize All By drop down, and all the data fields will be changed to the selected function.

pivot table summary functions

Sum Function 

The pivot table's Sum function totals all the underlying values for each item in the field. The result is the same as using the SUM function on the worksheet to total the values.

When you add a numerical field to the pivot table's Values area, Sum will be the default summary function. (Note: If the field contains text or blank cells, Count will be the default.)

In the screen shot below, you can see the source data for a small pivot table, and the total quantity, using the worksheet's SUM function, is 317.

pivot table source data

With a pivot table, you can quickly see the total sum for each product that was sold, and the grand total -- 317 -- which matches the worksheet total.

pivot table sum

Sum Function Macro

Instead of manually changing each data field to the Sum function, you can run a macro that will change the summary function in each data field.

You can copy this code to a regular code module in your workbook, and run it when you want to change the summary functions for all the Value fields.

Sub SumAllValueFields()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet

  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  Application.ScreenUpdating = False

    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf
    pt.ManualUpdate = False

  Application.ScreenUpdating = True
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
End Sub

Count Function

Count is the default summary function when fields with nonnumeric or blank cells are added to the Values area. The Count function's name is slightly confusing, because it's like the COUNTA worksheet function, not the COUNT worksheet function.

The pivot table Count function counts:

  • text
  • numbers
  • errors

Blank cells are not counted.

Select a Field to Count

If you're using the Count function in a pivot table, be careful which field you use for the count. For example, in the pivot table source data shown below, cell C7, in the Qty column, is blank.

pivot table count

If you want to count the order for Pens, and use the Qty field, the blank cell (C7) would not be counted. The number of orders for Pens would show as 1 (pivot table at left, below).

Instead, you could add the Product field to the Values area, and the Pens orders would show a count of 2 (pivot table at right, below).

pivot table count blank

Average Function

The Average function totals all the underlying values in the Values area, and it divides by the number of values. The result is the same as using the AVERAGE function on the worksheet to calculate the average (mean) of the values.

Blanks and Zeros

Blank cells are ignored when calculating the pivot table averages, but zero cells are included.

In the data source shown below, cell C7 is blank, and is not included in either the worksheet average (C12), or the pivot table average, shown below.

pivot table average data

If you have formatted the worksheet to hide zero values, remember that those zero values will be included in the averages, even if the cells appear blank.

Format the Results

When you use the Average summary function, the results will probably show a strange mixture of decimal places, as shown in the pivot table at the left, in the screen shot below.

Format the field to have a consistent number of decimal places (as in the pivot table at the right, below), so the numbers are easy to compare.

pivot table average format

Max Function

The Max summary function shows the maximum value from the underlying values in the Values area. The result is the same as using the MAX function on the worksheet to calculate the maximum of the values.

In the screen shot below, you can see the source data for a small pivot table, and the maximum quantity, using the worksheet's MAX function, is 97.

pivot table max data

With a pivot table, you can quickly see the maximum for each product that was sold, and the grand total -- 97 -- which matches the worksheet maximum.

pivot table max

Min Function

The Min summary function shows the minimum value from the underlying values in the Values area. The result is the same as using the MIN function on the worksheet to calculate the minimum of the values.

In the screen shot below, you can see the source data for a small pivot table, and the minimum quantity, using the worksheet's MIN function, is 8.

pivot table min data

With a pivot table, you can quickly see the minimum for each product that was sold, and the grand total -- 8 -- which matches the worksheet minimum.

In both the worksheet and the pivot table, the blank cell is ignored when calculating the minimum amount.

pivot table min

Product Function

The Product summary function shows the result of multiplying all the underlying values in the Values area. The result is the same as using the PRODUCT function on the worksheet to calculate the product of the values.

I've never had to use the Product summary function in a pivot table, and can't imagine a situation where it would be useful. However, you might have a use for it, and here's how it works.

In the screen shot below, you can see the pivot table source data, with the PRODUCT calculated for each product group. At the bottom of the source data is the overall PRODUCT calculation.

pivot table product data

The results of the Product function may be very large numbers and default to a Scientific number format. You can format the numbers as Number format, instead of Scientific format.

Note: Excel only stores and calculates with 15 significant digits of precision, so after the 15th character you'll only see zeros.

pivot table product

Count Numbers Function

The Count Numbers summary function counts all the underlying numbers in the Values area. The result is the same as using the COUNT function on the worksheet. Blank cells, errors, and text are not counted.

In the screen shot below, you can see the source data for a small pivot table, and the count of the numbers in the Qty column (column C). In cell C4, the value of 20 is entered as text, so that cell isn't counted.

pivot table count num data

Count Numbers vs. Count

In the pivot table shown below, the Qty field has been added twice to the Values area. In column B, the summary function is Count Numbers, and the Grand Total is 7.

In column C, the summary function is Count, which includes text, so the Grand Total for that column is 8.

pivot table count num

StdDev Function and StdDevP Function

Like the STDEV.P and STDEV.S worksheet functions, the StdDevp and StdDev summary functions calculate the standard deviation for the underlying data in the Values area. The standard deviation is a measure of how widely the values vary from the average of the values.

The StdDevP summary function should be used when the entire population is used in the calculation. When a sample of the data is used, not the entire population, then use the StdDev summary function.

In the screen shot below, you can see example pivot table source data, and the STDEV.P worksheet function is calculating the standard deviation for each product type. For the File Folders, there is a large difference between the quantities sold, and the standard deviation is high -- 44.5. For Paper, the difference in quantity is much smaller, and the standard deviation is low -- 4.7.

When the Qty field is added to the pivot table, change the summary calculation to StdDevp.

In the screen shot below, you can see that the standard deviations in the pivot table are the same as those that were calculated on the worksheet.

Note: If the count of items is one, a #DIV/0! error is displayed when using the StdDev summary function, because one is subtracted from the count when calculating the standard deviation.

How the Standard Deviation is calculated

For the standard deviation, each number is compared to the mean of the numbers. You could calculate the standard deviation on the worksheet, without using the STDEV.P function.

  1. Find the average of the numbers in the pivot table data.
  2. From each number, subtract the average.
  3. Square the calculated difference for each number
  4. Find the average of the squared difference.
  5. Find the square root of the average.

calculate standard deviation

Var Function and Varp Function

The Var and Varp summary functions work like the VAR.P and VAR.S worksheet functions, to calculate the variance for the underlying data in the Values area, and variance is a measure of how widely the values vary from the average of the values.

When the entire population is used in the calculation, the VarP summary function is used. For a sample of the data, instead of the entire population, use the Var summary function.

In the screen shot below is the example pivot table source data, with the VAR.P worksheet function calculating the variance for each product type. For the File Folders, where there is a wide difference between the two quantities, the variance is large -- 1980.25. For the paper sales, there is a small difference in quantity, and the variance is only 22.22.

To show the variance, when the Qty field is added to the pivot table, change the summary calculation to Varp.

As you can see, the variances shown in the pivot table are the same as those that were calculated on the worksheet.

Note: If the count of items is one, a #DIV/0! error is displayed when using the Var summary function, because one is subtracted from the count when calculating the variance.

How the Variance is calculated

For the variance, each number is compared to the mean of the numbers. You could calculate the variance on the worksheet, without the VAR.P function.

  1. Find the average of the numbers in the pivot table data.
  2. From each number, subtract the average.
  3. Square the calculated difference for each number
  4. Find the average of the squared difference.

calculate variance

Download the Sample Files

  1. To test the summary functions, you can download the Region Sales sample file. Create a pivot table from the data, and test the summary functions.
  2. To see the Sum All code, download the RegionSalesMacros file. It contains macros, and buttons to run those macros.

Xtreme Pivot Table Course

Pivot table skills are essential, if you want to be an Excel master. To raise your skills to the expert level, I recommend the Xtreme Pivot Table course, from John Michaloudis, at My Excel Online.

This course has more than 200 videos -- beginner, intermediate and advanced level -- along with practice workbooks, finance business cases, and 12 months of personal support. Each short lesson is clear, and easy to follow. Work through the lessons at your own pace, and track your progress. The course is an excellent value, at a surprisingly low price.

See the course details, and watch sample videos here: Xtreme Pivot Table Course. When you buy the course, use the coupon code CONTEXTURES for a 10% discount

More Pivot Table Resources

Tutorials:

 

Learn how to create Excel dashboards.

 

More Tutorials:

 

 

 

Contextures
Excel news
by email

 

 


Amazon.com 

 

 

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: July 19, 2014