Home >  Blog >  How to set the Sum function as the default in a PivotTable

How to set the Sum function as the default in a PivotTable

Posted by Colin on 1 September 2018

Let's address a very important issue faced by many people that use Microsoft® Excel®. Sometimes when working with PivotTables, the Count function is set as the default instead of the Sum function. This can be frustrating as you then have to set each column value to Sum. Here's how to solve this issue.

The problem is caused by having blank cells in the PivotTable source data, and as a result, the values default to count. In order to rectify the problem, you have to replace the blank cells with zero values. Here is how:

1. Click on one of the values in the source worksheet.

2. Press F5

3. Click Special.

4. Select Blanks and then Select OK.

 

5. Enter 0 in one of the blank cells.

6. Press CTRL + Enter.

A great tip to macro for your Pivot Table source data tables! Running this tip will mean that your Pivot Tables will default to sum, when created.

 

 

 

Author: Colin
Tags: Top Tip