Default Functions in a Pivot Table

Usually, when you add a numeric field to the Values area in a pivot table, it automatically uses the Sum function. Sometimes though, a field automatically uses the Count function.

Why does Excel use different functions are used for two fields that contain similar data?

  • If there are blank cells, or non-numeric data in the field, the Count function is used as a default.
  • For fields that contain all numeric data, the Sum function is the default.

Change the Function

Unfortunately you can’t set a default summary function in a pivot table. However, after the field has been added to the Values area, you can change its summary function:

  1. Right-click a cell in the field that you want to change, and click Summarize Values By.
  2. In the Summarize by list, select one of the functions.

Summarize Values By

Use Programming to Change Data to SUM

If you have too many fields to change manually, you can use a macro to change the summary function automatically. There is sample code here.

Use a Pivot Table Add-In

For an even easier solution, you can use my free pivot table add-in, that already has the summary function code in it. Just download and install the add-in, and run it when you want to change several fields, all at once.

__________

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.