Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    make zero vlaues format as dash (2000)

    I cannot recall (or search and find) how to make calculated values "vanish" - I want just a dash to show when the sum is zero. Tools/Options View choice does NOT work.

    The formatting is set to currency.

    Thx!

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: make zero vlaues format as dash (2000)

    Hi Randy,
    I always just click on the "comma style" format and the zero changes to a dash.
    Regards,
    Lana

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

    Re: make zero vlaues format as dash (2000)

    Select the range of cells you want to format, then select Format | Cellss... and activate the Number tab (or edit the Normal style).
    Select Custom in the Category list.
    Enter a format like this if you want to show numbers without decimals:
    <code>
    #,##0;-#,##0;"-"
    </code>
    The first section #,##0 in this custom format is for positive numbers, the second section -#,##0 for negative numbers, and the third section "-" for zero values.

    If you'ds like to display numbers with two decimal places:
    <code>
    #,##0.00;-#,##0.00;"-"
    </code>
    There are also some built-in formats that display zero values as a dash.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make zero vlaues format as dash (2000)

    can you tell me what the builtin formats are?

    I want the $ and 2 decimals just like the quick format currency style button usually shows - but I'd like to get the zero show not as $0.00 but as a dash.

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

    Re: make zero vlaues format as dash (2000)

    Try Financial (not Currency) format with two decimal places and with $ as currency symbol.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make zero vlaues format as dash (2000)

    Thx, but that must be something in newer versions - it is not in mine -- Excel 200 (aka 9.0 IIRC).

    Yet I see some cells that have that dash so I know it can do it... somehow.

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

    Re: make zero vlaues format as dash (2000)

    I'm very sorry, it's been a long day and I'm not very alert any more.
    I should have written Accounting format instead of Financial format.
    Attached Images Attached Images

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make zero vlaues format as dash (2000)

    I tried Accounting -- oddly, it works in one worksheet, but NOT in another. Both are in the same workbook or whatever Excel calls a file.

    Could something be overriding it in one worksheet??

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

    Re: make zero vlaues format as dash (2000)

    Could you post a small sample workbook?

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: make zero vlaues format as dash (2000)

    Are you sure the values are actually zero and not a very small decimal value?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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