Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table number format (Excel 2003)

    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>post 534,486</post:> 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot table number format (Excel 2003)

    Try the custom format
    <code>
    #,##0.0,</code>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot table number format (Excel 2003)

    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!)
    Attached Images Attached Images
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot table number format (Excel 2003)

    I don't have 2007, sorry.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot table number format (Excel 2003)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> <img src=/S/drop.gif border=0 alt=drop width=23 height=23> <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    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)
    Attached Images Attached Images
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot table number format (Excel 2003)

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

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot table number format (Excel 2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    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!!! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    No wonder Google did not come up with a solution. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> (It must have though - are you mad!!!)
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •