Excel + Google Sheets · Deep guide
IFERROR in Excel: hide errors with a clean fallback
Errors such as divide by zero or not available make a report look broken, even when the cause is harmless, like an empty row. IFERROR lets you decide what to show instead, for example zero or a short message.
IFERROR works in Excel 2007 or later and in Google Sheets. In this guide you will see how it works, a division example with real numbers and the cases where hiding an error is a bad idea. See also: VLOOKUP guide and SUMIF guide.
How IFERROR works
IFERROR takes two arguments: the formula that may fail and the value to show if it fails. Write it like this:
=IFERROR(A2/B2,0)If the division works, Excel shows the result. If it fails, because the divisor is zero or empty, Excel shows 0 instead of an error. Works in Excel 2007 or later.
Step by step example
- Type a numerator in A1 and a divisor in A2.
- Click an empty cell, for example C1.
- Type =IFERROR(A1/A2,0) and press Enter.
- Change A2 to 0 and see how the result becomes 0.
| A | B | |
|---|---|---|
| 1 | Numerator (A) | Divisor (B) |
| 2 | 10 | 4 |
| 3 | 5 | 0 |
With 10 and 4 the result is 2.5. With a divisor of 0 the result is 0 instead of an error. Use a short message such as none when 0 could be mistaken for a real value.
Common errors
- IFERROR hides every error, including real mistakes such as a wrong sheet name. Use it only around the formula that can fail.
- You show 0 where a blank would be clearer, and totals become misleading. Choose the fallback with care.
- You wrap a whole report in IFERROR and lose the signal that something is broken.
- The fallback is text but later formulas expect numbers, and they fail in turn.
A good rule is to fix the cause when you can and use IFERROR only when an error is expected, for example a lookup that may not find its key. Test with an empty row to confirm the fallback appears.
The same formula in other languages
- English IFERROR
- Español SI.ERROR
- Português SEERRO
- Deutsch WENNFEHLER