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:
= (D3 - C3) / C3.
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 displayed the #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 an 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:
See also this tip in French: Masquer les erreurs de formule dans Excel.
Please, disable AdBlock and reload the page to continue
Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.
