Zero values to show blank
 

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

Zero values to show blank
 

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Louise" wrote in message
...
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

Zero values to show blank
 

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

On May 3, 10:52 am, Louise wrote:
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

Zero values to show blank
 

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

"Louise" wrote:

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

Zero values to show blank
 

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

"FSt1" wrote:

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

"Louise" wrote:

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

Zero values to show blank
 

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
"Louise" wrote:

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

"FSt1" wrote:

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

"Louise" wrote:

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

Zero values to show blank
 

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

Thanks for the help - again!

Louise

"Pete_UK" wrote:

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

On May 3, 10:52 am, Louise wrote:
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

Zero values to show blank
 

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

On May 3, 12:46 pm, Louise wrote:
that would be a much quicker way, rather than using an IF on every formula.

Thanks for the help - again!

Louise



"Pete_UK" wrote:
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

On May 3, 10:52 am, Louise wrote:
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- Hide quoted text -

- Show quoted text -

Zero values to show blank
 

Louise

Would ToolsOptionsView uncheck "Zero Values" work for you?


Gord Dibben MS Excel MVP

On Thu, 3 May 2007 03:34:02 -0700, Louise
wrote:

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

"FSt1" wrote:

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

"Louise" wrote:

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

Zero values to show blank
 

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.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Louise" wrote in message
...
that would be a much quicker way, rather than using an IF on every
formula.

Thanks for the help - again!

Louise

"Pete_UK" wrote:

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

On May 3, 10:52 am, Louise wrote:
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