Pradhan,

Select:
Tools \ Options \ View Tab
Unselect <Zero Values>

Regards
Jacques

<<"but then if this cell is used in a subsequent formula, such as a sum, I
get a VALUE error">>

The SUM() function will still work with ( "" ) returned in a cell, included
in the sum range, from a formula.
You might have something else wrong to produce the #VALUE! error.

Apart from using Tools|Options|View and unchecking 'zero values', which
affects all cells on the worksheet, you can limit the effect to specific
cells with a custom number format. To do this, you could start with any of
the inbuilt number formats that have both +ve and -ve formats and simply add
a semi-colon (ie ';') to the end. Or you could roll your own, defining your
own +ve and -ve formats and adding a semi-colon (ie ';') to the end.

Cheers