Display Zero's (0) in blank cells

[michael.holohan]

I need to format an excel worksheet to show "Zero" in cells that are
blank.

I understand how to show them when you type in Zero (0) but cannot get
it to show Zero when the cell is blank.

Any ideas would be very helpfull

 

[John Coleman]

I don't think that there is any format setting that will do this for
you. It is possible to quickly convert blanks to cells without any
VBA:

1) select the range of cells that you are interested in
2) hit F5 or type CtrlG to get the GoTo window displayed
3) select special
4) select blanks - hit ok
5) type 0 then ctrl+enter

here is a vba sub to convert blank cells to zeros in a range of cells:

Sub Zero(R As Range)
Dim cl As Range
For Each cl In R.Cells
If IsEmpty(cl.Value) Then cl.Value = 0
Next cl
End Sub

Sub test()
Zero Range("A1:C5")
End Sub

you could probably link this with a change event if you want a cell to
display 0 when you hit it.

Hth

-John Coleman

 

[John Coleman]

I should point out that both of my suggestions *change* blanks to
zeros - which goes a bit deeper than changing how blanks are
displayed. In particular, many worksheet functions such as AVERAGE()
ignore blanks but treat zeros as values. It is possible to modify both
suggestions to have the *string* zero replacing blanks (with a change
in text alignment making them look like ordinary zeros) and this will
help (?) with some functions such as AVERAGE() but will still cause
some functions lkie COUNTBLANKS() to behave differently. The bottom
line is that it seems you can only display values, and a cell with a
value is no longer blank.

Hth

-John Coleman