Results 1 to 9 of 9
  1. #1
    fred5853
    Guest

    Zeros in blank cells (97)

    Can someone please tell me how to eliminate the zeros or hyphens from showing in cells that are part of my formula but do not contain data at this time.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Zeros in blank cells (97)

    Start with Tools, Options, View, uncheck the box marked "Zero Values" (XL97 menu).
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zeros in blank cells (97)

    If your formula is =A1+A2, then use:

    <pre>=IF((A1+A2)=0,"",A1+A2)
    </pre>

    Legare Coleman

  4. #4
    fred5853
    Guest

    Re: Zeros in blank cells (97)

    I have tried this but to no avail. I thought that would do it, but, alas I was mistaken. I do not know what the problem is. The cells contain a formula to be used at later dates, but at this time contain no data and are showing zeros or hyphens.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Zeros in blank cells (97)

    We're not done yet. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> See Legare's post, which is pretty much a guaranteed way to do what you want. You should also look at how the numbers are formatted. Formats using # characters do not show anything if there is no value for that digit location, formats using "0" characters show a zero if there is no value or zero for that digit location. What number formatting are you using?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Zeros in blank cells (97)

    Apart from the solutions given, you can use formatting to suppress the display of zero values. Format the cells as <pre><big> #,##0.00;-#,##0.00;<font color=red>""</font color=red></big></pre>

    The third element (shown in red above) of a format code determines how zeros are displayed and if you set that element to an empty string, the zeros should not display.

    Andrew C

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Zeros in blank cells (97)

    Additionally... Excel will still display 0's sometimes even when you do all that is mentioned. If the value is 0.000003 for example, and you have the display set to 2 decimal places... Excel will still display a zero.

    Dennis

  8. #8
    fred5853
    Guest

    Re: Zeros in blank cells (97)

    The cells are accounting formatted, no symbol, 2 decimals. I attended an Excel class a few weeks ago and the instructor told how to do this but it was 90' in the room with no tables to write on and it was 4 p. m. , no one in my group thought to write this tidbit down

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Zeros in blank cells (97)

    Fred5853, I hope you read Andrew Cronnoley's post. Here's what you can do. For the Cells you want formatted with zeros not showing, try these steps:

    Select the cells you want to change, select Cell, Formatting, Number, for simplicity make sure -all- those you want to set are formatted as Accounting, No Symbol, 2 Decimals, then select Custom.

    The Type box should read: "_(* #,##0.00_);_(* (#,##0.00);_(* "<font color=red>-</font color=red>"??_);_(@_)"
    or maybe, from your original question, it reads "_(* #,##0.00_);_(* (#,##0.00);_(* "<font color=red>0</font color=red>"??_);_(@_)"
    The red shown above is my edit, it won't show that way in the Type box.

    In the Type box, edit it to read: "_(* #,##0.00_);_(* (#,##0.00);_(* ""??_);_(@_)"; in other words, delete that character I have highlighted in red.

    Let us know if that doesn't work.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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