Pivot table number format (Excel 2003)

Topic

New Reply

Edited by HansV to provide link to post. It is *very* easy: I only had to put [ ] around the reference.

In a prior post post 534,486 I was shown a format trick to scale down millions to one comma. I’d like to learn how I can format 4,357,258 to look like 4,357.3 in my pt report. I can get to “0.#,” but cannot seem to get that scaling “,” after the 4 to show up all I get is 4357.3, which is not as useful. TI’ll keep trying. TYIA

Reply | Quote

Replies

Try the custom format

#,##0.0,

Reply | Quote

Hans,

I am aware of this number format and have used it many times in the passed in Excel 2003. (Formats like 0, and 0,, and #,0.0, etc). However, I see it is not working in 2007 when I tried to reply to Torquemada’s post. I spent the next 45 min trying to find a solution for 2007. Google does not seem to have much on the solution either. Any ideas on how to do the same format in 2007? See my attachment showing what 2007 does… (very strange!)

Reply | Quote

I don’t have 2007, sorry.

Reply | Quote

I was determined to get to the bottom of this and discovered (from other custom/number formats) that Excel 2007 uses a “space” to format for a thousands seperator. (Not the comma as in the 2003 version).

So the correct format to use is: (# ##0.0 )

or to be clearer: #_##0.0_ (please note that the _ is indicating a space)

Reply | Quote

The character used as thousands separator depends on the regional settings in Windows, it’s not an Excel setting. So I assume that the PC you are (or were) using has a space set as the thousands separator in the Regional and Language Options control panel (or whatever it’s called in Vista).

Reply | Quote

My laptop was set on SA settings (with the space) where the PC’s at my work are always on US regional settings (with the comma). And here I thought it was a Vista thing!!!

No wonder Google did not come up with a solution. (It must have though – are you mad!!!)

Reply | Quote