Francis has attended:
Excel Advanced course
Outlook Advanced course
Pivot Table Setup & Default Formats
How do you change the default setting for calculation type and cell format for results reported in a pivot table?
For example, when I drag fields into the bottom right box of the pivot table field list wizard 'Values' it always defaults to 'Count' and 'general' format. I would like the default to be 'sum' and number format with no decimal places and comma separator. The data source is number format so I am not sure why it defaults to Count instead of Sum. I can change this manually but it is tedious when experimenting with different reports.
Thanks,
Francis
RE: Pivot Table Setup & Default Formats
Hi Francis, thanks for your query. According to this:
http://www.contextures.com/xlfaqPivot.html #DefaultSUM
...you can't change the default settings for the data fields. "If a field contains blank cells, or cells with text, it will default to COUNT. Otherwise, it will SUM. There's a feature that changes all the data fields to SUM, in my PivotPower add-in, that you can download and install."
Try checking the formatting of your source data. The fact that Excel is defaulting to Count makes me suspect the data itself needs reformatting. Cut and paste the data unformatted into a new worksheet, reformat as numbers, then create a Pivot Table from that. Let me know how you get on.
All the best,
Anthony
