Pivot Table Setup & Default Formats

The UK's most regular instructor-led training courses.
Training information: microsoft.excel.training · Excel training courses · Microsoft Excel Training
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Medium Priority · Version 2007

1 post

ReplySat 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

Version	Date	Location	Places available	Book	Next place rate (£)		List price
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice	List price
2007	Mon 2 Aug 2010	Southwark	3		£240	£245	£245
2003	Thu 5 Aug 2010	Southwark	4		£240	£245	£245
2010	Fri 6 Aug 2010	Bloomsbury	0	FULL			£299
2007	Tue 10 Aug 2010	Bloomsbury	0	FULL			£245
2003	Tue 10 Aug 2010	Limehouse	5		£235	£240	£245
2007	Fri 13 Aug 2010	Limehouse	8		£229	£234	£245
Full Schedule: See all 134 Excel Advanced course dates. Bookings currently available until 28th September 2011.

452 posts

ReplyTue 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.

· Baffled by custom number formatting in Excel?
· Data Basics Using Excel
· More Tips From Excel's Rarely Used File
· Creating Smarter, Customized Invoices for your Small Business with Microsoft Excel
· Excel Course Tips and Tricks to Liven Up Your Excel Spreadsheets

Please browse our web site to find out more about
microsoft.excel.training and other Microsoft training courses.

Excel tip:

Using Alt in Save Dialog Box

When you are saving (or opening) a file, try these;
(Note the numbers are on the keyboard not the numeric keypad)
1. Go to previous folder Alt+1
2. Go up one folder level Alt+2
3. Search the Web Alt+3
4. Delete selected file Alt+4 or Delete
5. Create a new folder Alt+5
6. Cycle through all views Alt+6 repeatedly
7. Display the Tools menu Alt+7

View all Excel hints and tips