Pivot table number formatting

**anetsurfer** · 12-05-2009, 03:34 AM

Hi Everybody
I make & use pivot tables a lot.
Whenever I make one with numbers, I get formatting for a number as below
1234567.78(no commas and 2 decimal places)
I normally want the formatting as
1,234,568 (comma inserted, no decimals,), (the last digit is changed just becasue of round off, other wise number in both cases is same).

Now I can double click the field, goto number--> number and then apply this formatting.

My question is whether there is a setting in excel somewhere so that this formatting will come as default (after I create the pivot table each time)?
Requesting help
Regards
Anetsurfer

**Cheeky Charlie** · 12-05-2009, 04:50 AM

I don't think so, but then I don't know much.

Unless a better answer arrives, I could provide a macro for quickly applying (i.e. via keyboard shortcut) your desired number format to a pivot field - if you would like.

CC

**anetsurfer** · 12-05-2009, 05:21 AM

Hey CC
Please provide macro if possible
But please note that pivot table could be different each time.
Means there could be 1 to many data fields evert time.
Provide only if doesn't call for too many efforts.
Thanks again
Anetsurfer

**Cheeky Charlie** · 12-05-2009, 06:12 AM

Not too much effort...

Please Login or Register  to view this content.

Do you need instruction on using this?

CC

**anetsurfer** · 12-07-2009, 12:08 AM

Hey CC
It worked
A good work around
Thank you very much
Regards
Anetsurfer

**waternut** · 01-04-2010, 03:28 PM

I am having the same problem, however I need some help with some instructions on how to install this macro, and need to know what/if anything would be displayed if the value was negative and finally can the macro be altered to display a dash when a zero value is returned?

I know this an older post but any help is appreciated.

Thanks,

C.

**Cheeky Charlie** · 01-04-2010, 05:22 PM

Hi,

It can go in a standard module in your personal xls - do you know what this is?
Negative values would have a - on the front
For "-" for zero you'd need to change the format to:
"#,##0;-#,##0;-"

CC