If you use Pivot Tables then you will know the problem- I hope to help you with now. When you create your Pivot Table on certain columns of data, Excel will default to COUNT rather than the required SUM function. It is easy enough to change the function from COUNT to SUM but is there a way to default the data to SUM in the Pivot Table?.
Well, not automatically but there us a quick way to ensure Excel will Pivot with SUM with some preparation of your data before you go to Pivot.
If your columns of data contain text or blanks i.e non numeric data then Excel will default to COUNT. As I said there is no default setting to amend this but you can avoid the problem by ensuring that your non numeric cell contents are replaced with zero’s.
As ever with Excel this is easily done.
- Select the numeric columns in your original data
- Hit F5 and hit Special in GOTO Dialog box
- Select the Blanks option and hit Ok. Only the Blank cells will be selected, if you have some text in these columns also then go ahead and also select Constants and Text
- Hit Ok
- Type 0 and hit CTL+Enter
- Go back to your Pivot Table and hit Refresh
- That’s it
So to recap, ensure your columns of data are not blanks or contain text and Excel will default your Pivot to SUM!