98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot Table Setup & Default Formats
Pivot Table Setup & Default Formats
Resolved · Medium Priority · Version 2007
Sat 5 Dec 2009, 18:31Delegate Francis said...
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
Tue 8 Dec 2009, 09:01Trainer Anthony said...
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
Mon 14 Dec 2009: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Sum Up All the Values in A ColumnIf you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be: |
