Excel Tutorials
Replace ugly error codes like #DIV/0! and #N/A with a clean fallback value you choose, without changing the underlying formula. This lesson uses the Basic Budget workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
IFERROR wraps around an existing formula and catches any error it would otherwise produce, replacing that error with a fallback value or message you define instead. The formula underneath still runs exactly the same, IFERROR only changes what you see if it fails.
It's most useful for errors you actually expect to happen sometimes, like a division that might hit zero, or a lookup that might not find a match, rather than as a blanket fix for every formula in a sheet.
Step-by-step, matching the video above.
Common candidates: a division that could hit zero, or a lookup that might not find what it's searching for.
=IFERROR(original_formula, value_if_error), place your existing formula as the first argument exactly as it was.
Common choices are 0 for a number that feeds into a total, an empty string "" to show nothing at all, or a message like "Check Data" that tells you something needs attention.
Try inputs that would trigger the error and inputs that wouldn't, to confirm the formula still calculates correctly when nothing's actually wrong.
=IFERROR(first_attempt,IFERROR(second_attempt,"Not Found")) tries one formula, falls back to a second if the first fails, and only shows a fixed message if both fail.
| A: Spent | B: Budget | Formula | Result |
|---|---|---|---|
| 450 | 0 | =A2/B2 | #DIV/0! |
| 450 | 0 | =IFERROR(A2/B2,0) | 0 |
| 450 | 600 | =IFERROR(A2/B2,0) | 0.75 |
When Budget is 0, the raw formula throws #DIV/0!, but the IFERROR-wrapped version quietly returns 0 instead, useful when that value feeds into a chart or a running total that shouldn't break just because one row hasn't been budgeted yet. When there's no error, IFERROR passes the real result straight through unchanged, 0.75 in the third row.
They look almost identical, but catching every error isn't always what you actually want.
#DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, all of them get caught and replaced the same way, without telling you which one actually happened.
=IFNA(VLOOKUP(...),"Not Found") only replaces a genuine "no match found" result. A real mistake elsewhere in the formula, like a broken reference, still shows its actual error instead of getting silently hidden.
Wrapping a VLOOKUP in IFERROR will just as happily hide a typo in the range reference as it hides a legitimate missing match, IFNA only steps in for the specific case you're actually expecting.
Wrapping a broken formula in IFERROR makes the error disappear without fixing what's actually wrong. Use it for errors you genuinely expect, not as a way to stop worrying about a formula that shouldn't be failing at all.
If only one small piece of a long formula might error, wrapping the whole thing makes it much harder to tell what actually went wrong later. Wrap just the part that needs it when possible.
On a lookup formula, IFERROR will also swallow genuine mistakes like a bad range reference. IFNA only catches the "no match" case specifically, letting real errors still surface.
A broken formula shouldn't be the thing standing between you and a clean report.
Free 14-day trial. No credit card required.
IFERROR catches any error a formula would otherwise produce and replaces it with a fallback value or message you choose, instead of showing an error code.
=IFERROR(value, value_if_error). The first argument is the formula you want to run, the second is what displays if that formula errors.
IFERROR catches every kind of error, including genuine mistakes like a broken reference. IFNA only catches #N/A specifically, letting other error types still show up so real problems don't go unnoticed.
Yes, IFERROR catches every standard Excel error type, including #DIV/0!, #N/A, #VALUE!, #REF!, #NAME? and #NULL!.
No, it wraps around the existing formula without altering it. The original calculation still runs exactly the same, IFERROR only controls what displays if it fails.
Yes, one IFERROR can sit inside another as a fallback chain, trying a second approach before finally displaying a fixed message if everything fails.
Not always. Wrapping every formula in IFERROR can hide genuine bugs. It's best used for errors you actually expect, like a lookup that legitimately might not find a match.
All of them: #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL! are all caught the same way, without distinguishing which one occurred.
Every business starts with a spreadsheet. Updoot is where you scale past it, no formulas to catch errors for.
Start Your Free Trial →