Formatting blank cells as 0

[Laura G]

Is there an easy way to format blank cells as 0? I am
trying the use the formula =IF(ISBLANK(E3),0)and it keeps
creating a circular reference.

 

[Mark Graesser]

Call me crazy, but could you just type a zero?

Also check under Tools>Options>View and make sure Zero_Values is checked. If it isn't Excel wil supress the zeros.

Good Luck,
Mark Graesser

----- Laura G wrote: -----

Is there an easy way to format blank cells as 0? I am
trying the use the formula =IF(ISBLANK(E3),0)and it keeps
creating a circular reference.

 

[Max]

Just another view..

You could try a helper column?

Say, put in F3: =IF(ISBLANK(E3),0,E3)
then copy F3 down col F

(you won't hit the circular ref in this way)

And then refer to / use col F
instead of col E for downstream calculations

 

[Laura G.]

I guess I did not explain that there are over 1000 cells
in each column that I wanted to fill with 0. I was
looking for an easier way than typing it in each cell that
was blank. Some of the cells in the cells are not blank
so they need to keep their value. I think the ISBLANK
function will work or the ASAP utilities will fill the
blank cells also.

Thanks.

-----Original Message-----
Call me crazy, but could you just type a zero?

Also check under Tools>Options>View and make sure

Zero_Values is checked. If it isn't Excel wil supress the
zeros.

 

[Peo Sjoblom]

One way, select the whole range with blank and non blank cells,
press F5, click special and select Blanks, type 0 and press Ctrl + Enter,
press Ctrl + Home or just click in a cell