Using IFERROR in Excel: Examples and Common Fixes

Formulas & functions · Updated November 20, 2026

Use Excel’s IFERROR function to return a value you choose when a formula produces an error: wrap the calculation as =IFERROR(B2/C2,0). For example, if B2 contains 12 and C2 contains 0, the formula returns 0 instead of Excel’s #DIV/0! error.

What IFERROR does

IFERROR tests the result of a calculation or expression. If that result is not an error, Excel displays it normally. If it is an error, Excel displays the fallback value you specify instead. The function’s syntax is IFERROR(value, value_if_error): the first argument is what Excel evaluates, and the second is what it returns if the first argument produces an error.

The function can handle common Excel errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. In modern Excel, it can also handle errors from newer calculation features, such as #SPILL!. It does not repair the formula or correct the underlying data. It changes what appears in the cell when an error occurs.

IFERROR is useful when a known error condition is expected and a clearer result would help someone read or use the worksheet. It can also make a report less cluttered. But because it can hide unexpected errors, the fallback should tell readers something meaningful—or the formula should be checked before the error is concealed.

How to enter an IFERROR formula

In Excel for Microsoft 365 on Windows, select the cell where you want the result, type the formula, and press Enter. Replace the example cell references and fallback with the ones that fit your worksheet. To apply the formula to other rows, use the fill handle at the bottom-right corner of the selected cell, as with other formulas.

  1. Write the calculation you want Excel to perform as the first argument.
  2. After the comma, enter the value or message to show if that calculation returns an error.
  3. Close the parenthesis and check both the normal result and the error case.

For example, with revenue in B2 and units sold in C2, use =IFERROR(B2/C2,0). If B2 is 240 and C2 is 12, the result is 20. If C2 is zero, division is undefined and Excel returns 0 from the IFERROR formula instead. The example uses a zero fallback; that choice is appropriate only if zero is an understandable result for the people using the workbook.

Excel for Mac and Excel for the web use the same IFERROR function and formula structure. In some language or regional settings, Excel may use semicolons rather than commas between arguments. Follow the separator Excel expects in your installation; the examples here use commas.

Choosing a useful fallback value

The second argument can be a number, text, a cell reference, or another calculation. Choose it based on what the error means in the context of the worksheet—not just on what makes the error disappear.

  • Use 0 when zero is a reasonable substitute and will not be mistaken for a real measured value.
  • Use an explanatory message when a person should investigate, such as “Check input” or “Not found.”
  • Use an empty text string when an error should leave the displayed cell visually blank.
  • Use a meaningful alternative calculation only when it represents a valid result under the error condition.

With a numerator in B2 and a denominator in C2, this formula displays a blank-looking result when division fails: =IFERROR(B2/C2,""). The result is an empty text string, not a genuinely empty cell. For example, the cell still contains a formula, and other formulas may treat its result differently from a truly blank cell. A blank fallback can make a report look tidy, but it can also make missing or invalid data easy to overlook.

Practical example: preventing division errors

Division by zero is a common reason to use IFERROR. A zero denominator produces #DIV/0!; an empty denominator can lead to the same error in a calculation. A fallback prevents the error from appearing in the result cell, but deciding how to treat a zero denominator is a separate business or analysis decision.

With amounts in B2 and a divisor in C2, use =IFERROR(B2/C2,"Check divisor"). If B2 is 90 and C2 is 3, Excel returns 30. If C2 is 0, the formula returns the text “Check divisor.” This is often more informative than returning zero because it signals that the calculation needs attention.

For a column of calculations, enter the formula in the first result row and copy it down. Check that relative references such as B2 and C2 change to B3 and C3 in the next row. References with dollar signs, such as $B$2, stay fixed when copied, so use them only when the formula should continue to point to the same cell or range.

Using IFERROR with lookup formulas

Lookups commonly return #N/A when the requested item is absent. IFERROR can replace that error with a readable message. With product codes in A2:A100, prices in C2:C100, and the code to find in E2, use =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). If E2 matches a code in column A, the formula returns the corresponding value from column C; if no exact match is found, it displays “Not found.”

For a more targeted approach, wrap the lookup in IFNA when the only error you want to handle is a missing match. IFERROR catches other errors too, including a broken reference or an invalid argument. In the lookup example, a typo in the range or column index could also be hidden by “Not found,” even though the lookup setup—not the searched code—is the problem.

With lookup keys in A2:A100, return values in B2:B100, and the search key in E2, an INDEX and MATCH lookup can be written as =IFERROR(INDEX($B$2:$B$100,MATCH(E2,$A$2:$A$100,0)),"Not found"). MATCH uses 0 for an exact match. As with VLOOKUP, the fallback is helpful for an absent key, but it can conceal unrelated errors inside the lookup formula. When diagnosing a worksheet, temporarily remove IFERROR to see the original error.

IFERROR versus IFNA and ISERROR

IFERROR and IFNA are similar, but they do not catch the same range of problems. IFERROR substitutes a fallback for any error returned by its first argument. IFNA is narrower: it substitutes a fallback only for #N/A. That makes IFNA a good choice when a missing lookup result is expected but other errors should remain visible.

ISERROR is different again. It checks whether a value is an error and returns TRUE or FALSE; it does not itself provide a replacement result. This can be useful when a formula needs different actions depending on whether an error occurred. IFERROR is generally shorter when the requirement is simply “show this alternative if the calculation errors.”

Use the narrowest function that reflects your intent. For a lookup where only a missing key should be handled, IFNA can preserve useful warnings about unrelated formula problems. For a calculation where several error types have the same acceptable fallback, IFERROR may be simpler. Neither function validates whether a non-error result is logically correct.

Common problems and troubleshooting

A formula that displays a fallback is not necessarily fixed. If the fallback appears unexpectedly, remove IFERROR briefly or inspect the original expression to identify the source. A result of zero might mean division by zero, a missing lookup, or a different error, depending on the formula. That ambiguity can be risky in totals, dashboards, and analysis where readers may interpret zero as a measured value.

  • The fallback appears for every row: check the references, lookup values, and exact-match settings. The error may be widespread rather than exceptional.
  • A blank result is mistaken for missing data: consider a visible message or a separate status column instead of "".
  • A formula still shows an error: check whether the error is in the fallback expression itself, or whether the displayed text is merely characters such as “#N/A” rather than an actual Excel error value.
  • The workbook looks right but calculations are wrong: remember that IFERROR does not catch incorrect logic that returns a valid number or text.

Another misconception is that IFERROR handles every kind of problem in a cell. It responds to errors produced by the expression; it does not detect a wrong assumption, a mistaken but valid cell reference, or a number that is unreasonable but still valid. For example, a division formula might return 100 when the intended answer was 10 because the wrong cells were selected. IFERROR has no way to know that the result is incorrect.

Version and compatibility notes

IFERROR is available in Excel 2007 and later, including Microsoft 365, and in Excel for the web. A workbook that relies on it may not calculate properly in older spreadsheet software or versions that do not support the function. If a file must be used with older applications, check compatibility in the actual software and version used by its recipients.

The IFERROR function itself does not require a Microsoft 365-only feature. The formula examples here use familiar functions and syntax. Excel for Mac and the browser version can use IFERROR too, although menus and the surrounding interface may differ from Excel for Windows. The function’s behavior is the same for the examples discussed here.

Frequently Asked Questions

Does IFERROR hide all Excel errors?
It replaces an error returned by its first argument with the fallback you supply, including common errors such as #DIV/0! and #N/A. It does not identify incorrect results that are not errors, and an error in the fallback expression can still produce an error.

How do I show a blank instead of an error?
With the calculation in A2 divided by B2, use =IFERROR(A2/B2,""). This returns an empty text string on error. The cell still contains a formula, so it is not equivalent to deleting the cell’s contents.

Should I use IFERROR or IFNA for a lookup?
Use IFNA if you want to handle only a missing lookup result, which commonly returns #N/A. Use IFERROR if other errors from the lookup should receive the same fallback too. IFNA can make unexpected formula problems easier to notice.

Can IFERROR return text?
Yes. The fallback can be text in quotation marks, such as “Check input.” The cell then returns text when the first argument errors, which may affect later calculations that expect numbers.

Why does IFERROR return 0 when the formula should be blank?
Check the fallback argument: if it is 0, Excel is following the formula as written. To display a blank-looking result instead, use an empty text string as the fallback, such as "", while remembering that this is text rather than a truly empty cell.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.