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!
Why you NEED to know about Pivot Tables
Refresh all of your Pivot Tables with one click
Delete obsolete items from your Pivot Tables blog post or watch the YOUTUBE video..
Erik S says
Extremely helpful, and so very much appreciated – Thank you!!!!!
adam says
EXCEL-lent
Dan H says
Friggin’ fantastic, thanks a lot
Barbara says
Glad It Helped!
Barbara says
Glad It Helped!!
Darryl says
This has annoyed me for many years, I wish I tried to look up a resolution sooner. Thanks so much for your help.
Gunnar Olving says
Simply brilliant! Thanks a lot!
Angel says
Very helpful! Thank you!
Barbara says
Glad you find it helpful!
Regards
Barbara
bas says
Hi Barbara
you can also do this:
1 Define a name for the pivot table data (e.g. sourcepivot) via name manager
2 make sure the scope of the formula is for the workbook
3 in refers to enter the formula =OFFSET(‘SOURCE BW’!$A:$A;0;0;COUNTA(‘SOURCE BW’!$A:$A);COUNTA(‘SOURCE BW’!$1:$1)) –> in this formula “SOURCE BW” is the sheet name of your excel file
4 insert pivot table and select in “table/range” the name you defined in the name manager, in this example sourcepivot
5 customize your pivot as you want and you notice all output values are defined in SUM
6 in case your add columns to your original source file, the pivot will automatically be updated with these data based on the name manager function
John says
WOW – First hit in Google and mystery solved! This saved me a lot of clicking, thanks.
Barbara says
Glad it helped!
Regards
Barbara
joe says
Hi,
Thank you for many nice tips from you. I have the same problem. I still don’t understand is why M$ has not changed this annoying behavior to this day which has been quite a few years already. It would be much easier to be able to set the default to sum instead of this work around. For most people, the sum function is probably used more often than the count. It is ironic that Excel is such a powerful software but can’t do a simple thing like this. Maybe they probably don’t care.
uk classifieds says
Quite tidy web site content. Really looking forward to go through far more. Would like more.
Mel says
OMG! You’re the greatest. Been pulling my hair out for months doing this manually.
Jeaux says
Am I missing something? This didn’t work for me. I have all the blanks replaced with 0, but why would I replace my text? How will I have categories with which to group the #s?
Barbara says
Hello Jeaux
Thanks for your comment.
To get the Pivot to default to SUM requires that any of your data set does not contain any blanks or text characters. For exmaple you may have some rogue characters in your data set which would prevent the Pivot from SUM by default.
Regards
Barbara
Vishal says
If I select headings it automatically gets added to Row field. If I want them to get added to Value field, is it possible?
Barbara says
Hello Neeraj
By default By default, text fields are added to the Row Labels area, in a Pivot Table. You need to manually re arrange to achieve this.
Regards
Barbara
Abdul Saboor Sheikh says
Excellent and thank you very much.
Barbara says
You’re Welcome!
bas says
What you can also do is following:
1 open name manager:
2 Define a name, eg sourcepivot
3 selects scope: workbook
4 refers to: enter the formula =OFFSET(‘SOURCE BW’!$A:$A;0;0;COUNTA(‘SOURCE BW’!$A:$A);COUNTA(‘SOURCE BW’!$1:$1)) –> SOURCE BW is the sheet name of your pivot table source
5 Insert pivot table in your workbook and in table/range enter the name of the name manager, in this example sourcepivot
(6 in case you add columns or data to your pivot table, it will automatically update using the name manager option as it takes only rows/columns with data and thus also ignores count but always come with sum)
Rahul says
Thanks for support
Thomas says
Great post! Have nice day ! 🙂 5nsim