Zero values to show blank

[Guest]

Hi all

I have formulas on my worksheet and several of the 'answers' are zero, which
is correct, however, I want to format the whole worksheet so rather than
displaying '0', it simply leaves the cell blank.

How would I do this?

Thanks.
Louise

 

[Arvi Laanemets]

Hi

=IF(YourFormula=0,"",YourFormula)

 

[Pete_UK]

Hi Louise,

one way is to apply conditional formatting to the cells with the
formulae in, such that if the cell contents are zero then choose the
foreground colour to match the background colour (usually white) - a
zero cell will thus appear to be blank.

Hope this helps.

Pete

 

[Guest]

hi,
formating will not do that. all formating does is change the way data looks
on the sheet. It does not change the data nor can it make decisions based on
conditions.
try a formula like this.....

=IF(SUM(D13)=0,"",SUM(D13))

regards
FSt1

 

[Guest]

Hi
Thanks for the help.

If I want this to be applied to every cell in the worksheet though, where
would the IF statement go??

Louise

 

[Guest]

hi,
sorry. your can't apply this to every cell on the worksheet. that was what i
was trying to point out about formating. You would need to have a formula for
each sum.
You might try Pete_UK's suggestion as an alternative but you will not be
about to apply a decision to the entire sheet like formatting. it just don't
work that way.

Sorry
Regards
FSt1

 

[Guest]

that would be a much quicker way, rather than using an IF on every formula.

Thanks for the help - again!

Louise

 

[Pete_UK]

You're welcome. You can apply it to one cell and then use the Format
Painter to apply it to other cells, if they are not contiguous.

Thanks for feeding back.

Pete

 

[Gord Dibben]

Louise

Would Tools>Options>View uncheck "Zero Values" work for you?


Gord Dibben MS Excel MVP

 

[Arvi Laanemets]

Hi

But there may be a payback )))
Some formulas return error, when in their source range are errors. Hidding
them doesn't get you rid of them at all!

PS. As some oddity, there are situations, where you may deliberately create
errors in some table - p.e. to create a gap in line graph.