Cellwise

Excel guide · 3 min read

How to use IFERROR in Excel

Wrap your formula like this: =IFERROR(your_formula, value_if_error). If the formula works you see its normal result; if it returns any error, you see the second value instead. For example, =IFERROR(C2/B2,0) shows 0 instead of #DIV/0!. IFERROR works in Excel 2007 and later.

Sample data

Every formula on this page was calculated live against this sheet.

ABCDE
1SKUVisitsOrdersFind SKU
2TS-01120036TS-09
3TS-0200
4TS-0384551
5TS-040

Replace a #DIV/0! with 0 or a blank

Conversion rate is orders divided by visits. TS-02 had no visits, so C3/B3 divides by zero and shows #DIV/0!. IFERROR catches it.

Use 0 if you will sum or average the column. Use "" (two double quotes) for a cleaner report, but remember a "" cell is text, so =D3+1 on it gives #VALUE!.

fx=IFERROR(C3/B3,0)

Result: 0

TS-02 has 0 visits, so the division fails and IFERROR returns 0 instead.

Show a friendly message when a lookup fails

The most common use: a lookup for a value that is not in the list. Without IFERROR, the cell shows #N/A and anything that refers to it breaks too.

fx=IFERROR(VLOOKUP(E2,A2:C5,3,FALSE),"Not found")

Result: Not found

TS-09 is not in column A, so VLOOKUP would return #N/A. IFERROR shows Not found.

IFNA: only catch "not found"

IFERROR hides every error type, including #NAME? from a typo and #REF! from a deleted column. That can cover up real bugs.

For lookups, IFNA (Excel 2013 and later) is safer. It replaces only #N/A and lets every other error through, so you still see when the formula itself is broken.

fx=IFNA(VLOOKUP(E2,A2:C5,3,FALSE),"Not found")

Result: Not found

Same result here, because the only problem is a missing SKU. A typo in the formula would still show.

XLOOKUP has its own "if not found"

In Excel 365 and 2021, XLOOKUP takes a fourth argument for the not-found value, so you do not need a wrapper at all.

fx=XLOOKUP(E2,A2:A5,C2:C5,"Not found")

Result: Not found

TS-09 is missing, so XLOOKUP returns its own fallback: Not found.

Prefer a test over IFERROR when you can

If you know exactly what can go wrong, test for it. This only skips the division when visits are blank or zero, so any other problem still shows up as an error you can fix.

fx=IF(OR(B5="",B5=0),"",C5/B5)

Result:

TS-04 has a blank visits cell, so the formula returns an empty result rather than dividing.

Which to use when

Lookups: use XLOOKUP's if_not_found argument in Excel 365 and 2021, or IFNA in older versions. Division: test the divisor with IF, or use IFERROR if you just need a clean report. Everything else: fix the cause first and only add IFERROR where an error is expected and harmless.

A useful habit is to build the formula without IFERROR, check it works on a few rows, and only then add the wrapper. That way you know any fallback you see later is a real missing value, not a mistake in the formula.

Common mistakes

Wrapping every formula in IFERROR "just in case"

A misspelled function or deleted column then shows a calm 0 instead of an error. Use IFERROR on purpose, where you expect a specific error.

Returning "" and then doing math on it

An empty string is text. SUM ignores it, but + and * give #VALUE!. Return 0 if the cell feeds other calculations.

Leaving out the second argument

IFERROR needs both arguments. If you want a blank, type "" explicitly as the value_if_error.

FAQ

Which errors does IFERROR catch?

All of them: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!, plus #SPILL! and #CALC! in Excel 365. It cannot tell you which one happened.

What is the difference between IFERROR and ISERROR?

ISERROR only returns TRUE or FALSE. IFERROR returns your formula's result or a fallback in one step, which replaces the old =IF(ISERROR(x),"",x) pattern.

Does IFERROR slow down a workbook?

Not noticeably. It evaluates the formula once and only uses the fallback when there is an error, unlike the old IF(ISERROR()) pattern that calculated the formula twice.

Functions used

Keep learning

Updated 2026-10-31