Formatting blank cells as 0

  • Thread starter Thread starter Laura G
  • Start date Start date
L

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.
 
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.
 
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
 
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
Click to expand...
Zero_Values is checked. If it isn't Excel wil supress the
zeros.
 
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
 
Back
Top