Remove "0" when referencing a cell that is empty

[andrewharwood]

I'm creating a set of program plans for a sport and rec program that runs in multiple sites across the city on different nights.

To make this easier, I created a master planning sheet, and then created blank plans on for each program site on separate worksheets, and referenced them back to the first master planning sheet, and used the autofill feature to speed up the process, filling in each cell

Now, I've got 0's in all the places that do not have anything written in them. Is there a way to format the cells so that the 0 does not show up when the cell is empty?

I attached pictures of the worksheets to make it easier to understand.

[oeldere]

If you're working with an formula you can do:

If(yourformula=0,"",yourformula)

[TMS]

Or, if your formula is a simple =Sheet2!A1, use: =IF(Sheet2!A1="","",Sheet2!A1)


Regards, TMS

[vlady]

if you just want to hide the "0" with our converting it to null

there is an option in excel 2003

Under Tools -> Option -> View tab ---untick the "zero values"...

[TMS]

@vlady: wouldn't that also hide genuine zeros from the second sheet and on the primary sheet?

Regards, TMS

[vlady]

yup all zero values on all (second sheet and on the primary sheets).

[Kevin UK]

Hi andrewharwood

Another way is to format the cells: Custom > If they are general, add 2 semi-colons after general "general;;" or "0.00;;" or what ever formatting you require.