Hi,

Does anyone know how to format a cell so that it looks empty when a zero is
put in?

I am using the custom number format of #,##0;(#,##0) so that I can put
company accounts onto a sheet, where negative values appear in brackets. I
want to extend the format so that zeros appear as blanks (or rather, they do
not appear).


When turnover is zero, so far, if I leave the cell blank, the average
function does not take it into consideration. On the other hand, I do not
want any zeros to "appear" on my page or my hard copies as there are lots of
them.

Hard enough as it is, finally, is there also a way to edit my format to make
zeros appear as any other character, for example a hyphen "-" ?

Thank you for your help.

Rajen

Tools - Options - View tab and uncheck Zero Values
--
HTH...

Jim Thomlinson


"RajenRajput1" wrote:

Hi,

Does anyone know how to format a cell so that it looks empty when a zero is
put in?

I am using the custom number format of #,##0;(#,##0) so that I can put
company accounts onto a sheet, where negative values appear in brackets. I
want to extend the format so that zeros appear as blanks (or rather, they do
not appear).


When turnover is zero, so far, if I leave the cell blank, the average
function does not take it into consideration. On the other hand, I do not
want any zeros to "appear" on my page or my hard copies as there are lots of
them.

Hard enough as it is, finally, is there also a way to edit my format to make
zeros appear as any other character, for example a hyphen "-" ?

Thank you for your help.

Rajen

And to think of all of the times I thought I was being so clever by using
Conditional Formating to make the font white on white when the value =0.

Feel the release!

"Jim Thomlinson" wrote:

Tools - Options - View tab and uncheck Zero Values
--
HTH...

Jim Thomlinson


"RajenRajput1" wrote:

Hi,

Does anyone know how to format a cell so that it looks empty when a zero is
put in?

I am using the custom number format of #,##0;(#,##0) so that I can put
company accounts onto a sheet, where negative values appear in brackets. I
want to extend the format so that zeros appear as blanks (or rather, they do
not appear).


When turnover is zero, so far, if I leave the cell blank, the average
function does not take it into consideration. On the other hand, I do not
want any zeros to "appear" on my page or my hard copies as there are lots of
them.

Hard enough as it is, finally, is there also a way to edit my format to make
zeros appear as any other character, for example a hyphen "-" ?

Thank you for your help.

Rajen

That is brilliant - Both the answers.

1. Conditional format the whole lot for white text if value =0.

2. Tools - Options - View - uncheck zero value
on Excel 2007 this is in the Excel Options (in top left circle, then
options)- advanced - display options for this worksheet

3. I managed to amend my format to #,##0;(#,##0);_*- and it worked as a
"-" and if i clear the "-" from the format then it disappears. I am not sure
if this has any negative effects or disadvantages.

Thank you for all your help !!

Averages do not work if you miss out a cell - must put the zero in !!

Rajen


"Bigfoot17" wrote:

And to think of all of the times I thought I was being so clever by using
Conditional Formating to make the font white on white when the value =0.

Feel the release!

"Jim Thomlinson" wrote:

Tools - Options - View tab and uncheck Zero Values
--
HTH...

Jim Thomlinson


"RajenRajput1" wrote:

Hi,

Does anyone know how to format a cell so that it looks empty when a zero is
put in?

I am using the custom number format of #,##0;(#,##0) so that I can put
company accounts onto a sheet, where negative values appear in brackets. I
want to extend the format so that zeros appear as blanks (or rather, they do
not appear).


When turnover is zero, so far, if I leave the cell blank, the average
function does not take it into consideration. On the other hand, I do not
want any zeros to "appear" on my page or my hard copies as there are lots of
them.

Hard enough as it is, finally, is there also a way to edit my format to make
zeros appear as any other character, for example a hyphen "-" ?

Thank you for your help.

Rajen