Replace formula values with a blank

**4joey1** · 07-11-2010, 10:44 AM

Hallo!I am dealing with large Excel Sheets that have lots of formulas in them.
When I apply formulas to the data,some rows have zero values,these zero values make my reports look untidy since the too many zeros confuse the readers of the reports.

I would like to retain the formulas but delete all these superfluous zeros.In my attachment Budget 2010-2011,the rows that have been highlighted in green have zero values that are useless and need to be removed.

I need one formula or tip to remove these zeros and replace them with blank cells without compromising the formulas in the worksheet.

Please help me achieve this.

Thanks.

**JBeaucaire** · 07-11-2010, 11:24 AM

If you find zeros unattractive, turn off their display in TOOLS > OPTIONS > VIEW > [ ] Zero Values.

I've used that for years with no ill effects. Others don't like it, it's your call.

**abousetta** · 07-11-2010, 12:05 PM

Adding to JB's post, if you only want to modify some of the forumlas to not show zeros but not all formulas, then you could add another If statement. Example, you could change U15 from:

=IF(ISERROR(T15)," ",T15)

to:

=IF(ISERROR(T15)," ",IF(T15<>0,T15,""))

This will only affect the formula in U15 but not globally because I noticed you have no problem with zeros in other cells.

Hope this helps.

abousetta