IFERROR in Excel Not Working? Causes and Practical Fixes

If IFERROR is not working in Excel, first check that the cell contains a formula that actually returns an Excel error, and confirm the formula’s parentheses and argument separator are correct. IFERROR only replaces error values with the fallback you specify; it does not fix incorrect results, plain text that looks like an error, or a formula that Excel is not calculating.

What IFERROR does—and what it does not do

IFERROR evaluates an expression and returns its result when that evaluation succeeds. If the expression returns an Excel error, it returns the fallback value you supplied instead. Common errors include #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. The function is useful for making reports more readable, but a clean-looking result can hide a problem in the underlying calculation.

For example, with a sales amount in A2 and a quantity in B2, this formula displays a message if dividing by the quantity produces an error. If A2 contains 120 and B2 contains 0, the result is “Check units.” If B2 contains 4, the result is 30.

With the sales amount in A2 and the quantity in B2, use:

=IFERROR(A2/B2,"Check units")

IFERROR does not judge whether a result makes sense. If the formula returns 0 when you expected 30, but does not produce an Excel error, IFERROR returns 0. It also does not catch a cell that simply contains the characters #N/A as text. The displayed text may look like an error, but Excel does not treat it as an error value.

Check the formula and its separators

Start by selecting the problem cell and reading the formula bar. Confirm that the formula begins with an equals sign, IFERROR is spelled correctly, and its arguments are separated appropriately. In standard English-language Excel settings, the arguments are separated with commas. Some regional settings use semicolons as the list separator, so a formula copied from another computer may need its separators changed to match the current installation.

Check that the formula has two arguments: the expression to evaluate, followed by what to show if that expression errors. The parentheses must enclose the complete expression and fallback. If the original formula has several functions nested inside it, verify which part you intend IFERROR to cover. A misplaced parenthesis can leave a calculation outside the error handler.

Also check whether the cell is formatted as Text or whether the formula begins with an apostrophe. In either case, Excel may display the typed characters instead of evaluating the formula. Change the cell format to General, edit the cell, and press Enter to make Excel parse the entry again. Simply changing the format may not convert a formula already stored as text until you re-enter it.

Find out whether the result is an error at all

A common reason for an apparent failure is that the inner expression does not produce an error. A blank cell used as a divisor, for example, may be treated as zero and cause #DIV/0!, which IFERROR can replace. But an empty string returned by another formula, a legitimate zero, or a text value may not be an error. IFERROR will return that value unchanged.

Test the expression on its own in a spare cell or temporarily remove the IFERROR wrapper. This reveals the original result and makes it easier to distinguish a true Excel error from a wrong-but-valid answer. If the expression returns a normal value, investigate its logic or input data rather than changing the fallback message.

Look closely at error-like text in imported data, too. A cell can contain the characters “#VALUE!” without holding Excel’s #VALUE! error. Other clues include a leading apostrophe, left-aligned numbers, or values that do not behave like numbers in calculations. Convert or clean the source data where appropriate; wrapping every calculation in IFERROR may conceal the underlying data-quality problem.

Check calculation mode and formula display

If the formula is correct but its displayed result seems stale, Excel may be using manual calculation. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and select Automatic if that is the intended setting. Pressing F9 recalculates formulas in an open workbook, but it is better to confirm the workbook’s calculation setting if values repeatedly fail to update.

Another possibility is that Excel is showing formulas instead of their results. Check Formulas > Formula Auditing > Show Formulas. If that view is enabled, cells display formula text across the worksheet, which can look like formulas are not evaluating. Turn the option off to return to result display.

Calculation mode is a workbook-level concern, not a special property of IFERROR. A formula may be accepted and still show an earlier result until recalculation occurs. This is especially worth checking after opening a workbook created in another file or after changing a calculation-heavy workbook’s settings.

Diagnose the error inside the formula

When you remove the wrapper and see an error, identify its cause before deciding whether to hide it. #DIV/0! usually points to a zero or blank divisor. #REF! means a reference is invalid, often because a referenced row, column, or sheet was deleted. #NAME? can indicate an unrecognized function name, a misspelled name, or text that was not enclosed in quotation marks. #VALUE! often signals an incompatible value or argument type.

For a lookup that may not find a match, use a fallback that explains the expected situation. Suppose A2:A4 contains the IDs 101, 102, and 103; B2:B4 contains their names; and E2 contains the ID to find. With 103 in E2, the formula below returns the name in B4. With an ID absent from the list, it returns “No match.”

With IDs in A2:A4, names in B2:B4, and the lookup ID in E2, use:

=IFERROR(VLOOKUP(E2,A2:B4,2,FALSE),"No match")

This version also hides errors other than a missing match, including a broken reference or a malformed lookup range. That may make troubleshooting harder. If the goal is to handle only a missing result, use IFNA instead; it leaves other kinds of errors visible so they can be investigated.

Choose the right fallback instead of hiding everything

Use a fallback that helps someone understand what happened. A label such as “Check units” or “No match” is usually more informative than an empty string. A blank-looking result can make a report appear tidy, but it may also make an incomplete calculation easy to overlook. Use an empty string only when an intentionally blank display is suitable and users have another way to detect missing or invalid data.

With the same lookup layout—IDs in A2:A4, names in B2:B4, and a lookup ID in E2—this formula returns “No match” only for #N/A. If the lookup range itself contains another error, that error remains visible:

=IFNA(VLOOKUP(E2,A2:B4,2,FALSE),"No match")

IFERROR is broader: it handles any error returned by its first argument. IFNA is narrower and is therefore often preferable for lookup formulas where a missing match is expected but other faults should not be concealed. Neither function corrects the source problem; they only determine what value Excel displays when the specified error occurs.

Other causes: references, nesting, and version differences

If a formula uses a cell that itself contains an error, IFERROR can catch that error when the reference is part of its first argument. However, the fallback can fail too. If the fallback expression contains a bad reference or another error-producing calculation, Excel can display that error instead of the intended message. Keep the fallback simple when possible.

Also look for a circular reference: a formula that depends on its own result, directly or through other cells. IFERROR is not a way to resolve circular logic. Excel may warn about the circular reference or calculate an unintended result, depending on workbook settings. Trace the referenced cells and restructure the calculation so its inputs do not depend on its own output.

IFERROR is available in modern Excel, including Microsoft 365. If a workbook is opened in a much older spreadsheet program that does not support the function, the application may show an unsupported-function error. Excel for the web and Excel for Mac support IFERROR as well, although some ribbon labels and regional separator settings can differ from Excel for Windows. For a current Microsoft 365 workbook, a formula that works on one platform but not another is more likely to involve separators, data, calculation settings, or compatibility with a different application than an IFERROR-specific setting.

A practical troubleshooting sequence

Use this order to isolate the cause without masking useful evidence:

Select the cell and inspect its formula in the formula bar. Confirm it is being evaluated, not stored as text.
Check the commas or semicolons required by the computer’s regional settings, along with both arguments and all parentheses.
Temporarily inspect the inner expression without IFERROR. If it has no error, troubleshoot the result or the source data instead.
If an error appears, identify its type and fix the reference, input, lookup, or calculation that caused it.
Confirm the fallback is appropriate and does not itself produce an error. Use IFNA when only #N/A should be handled.
If the result is out of date, check automatic calculation and whether Show Formulas is enabled.

After troubleshooting, test both paths: a normal input that should return a value, and an input that should trigger the fallback. This catches formulas that hide the expected error but also accidentally change ordinary results. If other people rely on the workbook, use a clear fallback message and document what users should check rather than silently returning a blank.

Frequently Asked Questions

Why does IFERROR still show #DIV/0!?
Check that the error-producing calculation is inside IFERROR’s first argument and that the parentheses close in the right places. Confirm that Excel is evaluating the entry as a formula, and verify the separator expected by your regional settings. If the fallback expression itself produces an error, that error can still appear.

Does IFERROR hide every Excel error?
It returns its fallback when the first argument evaluates to an Excel error, including common errors such as #DIV/0!, #N/A, #REF!, and #VALUE!. It does not hide ordinary text that merely looks like an error, nor does it correct a wrong result that Excel calculated without an error.

Why does IFERROR return a blank?
The formula may specify an empty string as its fallback, or the first argument may itself return an empty string without error. A blank display does not prove that IFERROR caught an error. Inspect the formula and test the first argument separately to see which case applies.

Should I use IFERROR or IFNA for a lookup?
Use IFNA when the intended exception is a missing match that produces #N/A, because it leaves other errors visible. Use IFERROR when you deliberately want the same fallback for all errors from the expression. For troubleshooting or audited workbooks, the narrower option can make unexpected problems easier to spot.

Why does IFERROR work on one computer but not another?
Check each computer’s list separator and whether the other application supports the function. Regional settings can require semicolons instead of commas, and non-Excel programs may differ in function support. Also compare calculation mode, formula-as-text formatting, and the actual values in referenced cells.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.