Select the range containing the table with blank cells.
Press Ctrl+G (GoTo).
Click Special.
Select Blanks and click OK.
Type a zero, then hold Ctrl while pressing Enter.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/WordPress/


On 9/17/2010 2:23 PM, shriil wrote:
I have an excel table which gets populated from another excel table.
After import the table looks like:

A1 B1
C1 O1
budget head department apr may jun jul aug sep
oct nov .... total

2a ops 11.2 0.4
1.1 4.2 16.9

2b mtc 3.1 0.7 5.5
8.6 1.2 0.5 19.6

... ... ,, ... ... ... ..

The table range is from A1: O50

As can be seen, some of the cells are blank as there was no data from
the imported table. I would like to convert these blank cells to zero
(0), instead of keeping them blank. This is because I use these table
for finding out individual department cost -budget head wise, for
which I use the sumproduct function. What I observe that while
evaluating a sumproduct formula, when the formula is encountering a
blank cell, it is giving an error (#Value), whereas when the blank
cell is replaced by a zero, the formula is working.

It would be great if I could run a macro or a code where the blank
cells in the range are looked up and replaced with the value 0.

Thanks for the help