Change Default Function COUNT to SUM in Pivot Table in Excel.

Excel Best Trick to Make SUM as Default Function in Pivot Table instead of COUNT, How to Make Pivot Table Default To SUM Not COUNT.

When Pivot Table is used in Excel by default it shows the COUNT of the values. But if you want to SUM the values in your data every time you use Pivot Table then how is it possible, what should we do to make Pivot Table Default to SUM instead of COUNT.

Everyone who has ever used Pivot Tables has encountered a common problem. Excel uses the COUNT function by default in subtotals rather than SUM, when you add certain fields to the Pivot Table.

Pivot Table

So, Here is an easy, quick and best trick to change the function from COUNT to SUM in Pivot Table.
However, there is  a method to do this. You can easily change the function from COUNT to SUM by right-clicking the column in the Pivot Table. But to do this for many column would be a lot of work.

Pivot Table Default settings COUNT or SUM?

Due to the columns in the original data table those contains non-numeric data (either text or blanks), the Excel uses the COUNT function for those columns in a Pivot Table.

There is no way / method to change default setting of Excel for this, however, by ensuring that your original data table does not include text or blanks in numeric columns you can avoid this problem.
But replacing all those blanks cells with zeros is going to be a big chore / problem. Well, it is not like that.





Method to Replace blank cells with zeros in Excel Spreadsheets. Follow the given below method to replace blank cells with zeros.


1) Select all of the (numeric) columns of data in your original source data;

2) Press the F5 key on your keyboard and click the Special... button in the Goto dialog;

3) Select the Blanks option*** and click OK. Only the blank cells are now selected;

Go to Blanks

How to select only blank cells in Excel 2003, 2007, 2010, 2013?

4) Type 0, hold down the CTRL key and press Enter. All blank cells are now filled with zeros. Note that holding down the CTRL key when pressing Enter forces Excel to enter the same value (or formula) in all selected cells;

5) Now go back, right click anywhere in your Pivot Table and select Refresh.

*** If any numeric columns in your source data contain text, repeat the steps above but instead select the Constants and Text options in Step 3.

How to select only text cells in Excel 2003, 2007, 2010, 2013?

Go to Text