Thread: Zeros in blank cells (97)

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

3. Re: Zeros in blank cells (97)

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

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

4. 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. 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?

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

Posting Permissions

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