Hide formula errors in Excel
For example, if you have a spreadsheet where you track monthly sales as a percentage:
The formulas in column E do the calculations of percentages. For example, cell E3 contains the formula:
The formula in E4 was simply copied down the column to handle the other data.
The formula is correct for all cells, but in E10:E14 it is displays #DIV/0! error message, because of empty cells in C10:C14.
You can avoid displaying formula errors by changing the formula to use an IF and an ISERROR function:
= IF ( ISERROR ( <formula> ), “”, <formula> )
To display a blank if this formula returns error. In this example:
= IF ( ISERROR ( (D3-C3)/C3 ), "", (D3-C3)/C3 ):
If you prefer, you can replace the empty string ("") with zero or other text of your choice, just make sure the text is enclosed in quote marks. For example: