The IFERROR function
IFERROR replaces an error with whatever you want: an empty cell, a zero, a message. Handy, and dangerous: it hides every error, the expected ones and the real ones.
Syntax
=IFERROR(value, value_if_error)
In a French Excel: =SIERREUR(valeur; valeur_si_erreur).
Arguments
- value: the formula to watch.
- value_if_error: what to return if it ends in any error.
An example
=IFERROR((C2-B2)/B2,"")
The percentage change, and an empty cell while B2 is empty or zero.
Pitfalls
- It hides everything: a typo (#NAME?), a broken reference (#REF!) disappear too. The table looks right; it is not.
- Around a lookup, IFNA only hides #N/A, "not found", and lets the rest show.
- A replacement zero skews averages: prefer an empty cell.
Going further
- The #N/A error and the #DIV/0! error.
- The formula audit: a workbook's errors, even those hidden elsewhere.
Frequently asked questions
- IFERROR or IFNA?
- IFNA around a lookup: only absence is hidden. IFERROR for a calculation whose only possible error is known, such as a division.