Zero values to show blank

L

Louise

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
 
P

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
 
F

FSt1

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(D1:D3)=0,"",SUM(D1:D3))

regards
FSt1
 
L

Louise

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
 
F

FSt1

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
 
L

Louise

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

Thanks for the help - again!

Louise
 
P

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
 
G

Gord Dibben

Louise

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


Gord Dibben MS Excel MVP
 
A

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