Excel IFERROR Not Working? Fix Common Formula Problems

Excel IFERROR usually seems not to work because its first argument is not producing an Excel error, the formula is stored as text, or the formula has a syntax or calculation problem. IFERROR replaces errors returned by a calculation; it does not correct wrong-but-valid results or hide every kind of problem in a worksheet.

What IFERROR does—and what it does not do

IFERROR evaluates a value or formula and returns an alternative result if that evaluation produces an Excel error. The errors it can handle include common results such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. It is useful when a calculation may legitimately fail for some rows, such as a division where the denominator can be zero or a lookup where a key may be missing.

It is not a general-purpose formula repair tool. If a calculation returns a number that is mathematically or logically wrong, Excel sees a result—not an error—and IFERROR leaves it alone. It also cannot rescue a formula that Excel cannot parse, such as one with a missing parenthesis or an invalid argument separator. Those problems must be corrected in the formula itself.

Another common misconception is that IFERROR makes an error disappear from the workbook. It only changes the result shown by the formula containing it. The underlying issue may still exist in the source data or referenced formula, so using a generic replacement such as zero can make a report look correct while concealing a data problem.

Check the formula structure and arguments

With a value of 120 in A2 and 4 in B2, the following formula returns 30. If the division produces an Excel error instead, it returns the text Check inputs.

With the numerator in A2 and the denominator in B2, enter:

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

IFERROR takes two arguments: the calculation to evaluate and the value to return if that calculation errors. The second argument can be text, a number, a reference, or another formula. If it is omitted, Excel returns a zero for an error, which may be mistaken for a successful calculation. For clarity, specify the fallback you actually want.

Check that the calculation that can fail is inside the first argument. For example, placing IFERROR around an unrelated cell will not catch an error in another calculation. Also inspect parentheses and separators. Microsoft 365 for Windows commonly displays commas between arguments, but Excel follows regional list-separator settings. In some locales, formulas use semicolons instead. If a comma-based formula is rejected at entry, check the computer’s regional settings rather than assuming IFERROR is unavailable.

Find out whether the result is really an error

Look at the original value or calculation before adding more layers of error handling. If a cell displays a message such as Not found but contains ordinary text, IFERROR will not replace it. Likewise, an empty cell, a zero, or a blank string returned by another formula is not automatically an error.

Test the source expression by itself in a spare cell or temporarily remove the outer IFERROR. If the result is a normal value, investigate the logic: perhaps a lookup is finding the wrong row, a date is being interpreted unexpectedly, or a division is using the wrong denominator. IFERROR cannot tell whether a result is sensible; it only detects Excel’s error values.

A blank-looking result can also be intentional. If the fallback is an empty text string, the cell appears empty but is still occupied by a formula. That distinction matters for later formulas, counts, charts, and data-entry checks. Use a descriptive message while diagnosing the workbook, then decide whether a blank is truly the best final display.

Common reasons IFERROR appears broken

The formula is text, not a live formula. A leading apostrophe or Text number format can make Excel display the formula characters instead of calculating them. Change the cell format to General, then edit and re-enter the formula. Merely changing the format may not convert an existing text entry.
Show Formulas is enabled. Excel may display formulas across the worksheet instead of their results. On Windows, press Ctrl+` (the grave-accent key, often beside 1) to toggle Show Formulas. On Mac, use Control+` where available, or check Formulas > Show Formulas. The displayed formula is not proof that IFERROR failed.
Calculation is set to Manual. A formula may show an old result until recalculated. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. On Mac, use the Formulas tab and its Calculation Options control if shown. Excel for the web generally recalculates formulas automatically.
The error happens outside the wrapped expression. A separate formula, a downstream calculation, or a cell reference may be producing the visible error. Trace the exact cell showing the error and check its formula rather than wrapping an entire sheet indiscriminately.
The fallback itself is not what you expect. The second argument is returned only when the first argument errors. Check that it contains the intended text, value, or calculation and that the fallback calculation does not itself produce another error.
A formula-entry issue is being mistaken for an IFERROR issue. Missing parentheses, unmatched quotation marks, an unsupported function elsewhere in the expression, or the wrong regional separator can prevent the formula from being accepted or evaluated.

Handle lookups without hiding useful information

A missing lookup is a common reason to use IFERROR. With lookup keys in A2:A10, return values in B2:B10, and the search key in E2, this formula returns the matching value or Not found when VLOOKUP returns an error:

With keys in A2:A10, results in B2:B10, and the requested key in E2, enter:

=IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")

For example, if E2 contains a key that is absent from A2:A10, the result is Not found. If the key exists and its corresponding B-cell contains a value, that value is returned. However, IFERROR also hides other errors in the lookup expression, such as an invalid reference. If you want to treat only a missing match as expected, IFNA is more precise because it handles #N/A and leaves other error types visible.

Be careful about replacing lookup errors with zero or an empty string in reports. A zero may look like a genuine measurement, and a blank may make missing records hard to spot. A message such as Not found is often safer during data cleanup. Once you know why matches are absent, you can choose an appropriate final treatment.

Choose an appropriate fallback

The right fallback depends on what the error means in the context of the worksheet. Consider whether the error is expected, whether a reader needs to know it occurred, and whether a substitute could be mistaken for real data.

Use a clear message when the user should investigate a missing input or match, such as Check denominator or Not found.
Use a blank result when an unavailable value should not appear in a presentation, but document or separately track the missing data if it affects decisions.
Use zero only when zero is a valid and intended substitute. Do not use it merely to make a column look tidy; it can distort totals and averages.
Leave the error visible during development or auditing if the error signals a broken reference, incorrect data type, or unexpected condition that needs correction.

For instance, a division-by-zero error may mean there are no units to calculate a rate for, not that the rate is zero. A text result such as No rate is available can communicate that distinction better than a numeric zero. The correct choice depends on how later formulas and people will interpret the cell.

When to use IFNA or another approach

IFERROR is broad: it catches many kinds of calculation errors. That breadth is convenient, but it can hide problems unrelated to the condition you intended to handle. Use IFNA when the specific expected failure is a missing match that returns #N/A. Then a #REF! or #VALUE! error remains visible and can be fixed rather than silently replaced.

For a known condition such as a zero denominator, an IF test can express the rule directly. With a numerator in A2 and a denominator in B2, this returns a message only when B2 is zero; otherwise it calculates the division:

With the numerator in A2 and denominator in B2, enter:

=IF(B2=0,"No rate",A2/B2)

This approach makes the intended condition explicit, though it does not guard against every possible error in the division. For example, a different invalid input could still produce an error. Choose the method that matches the actual rule: IFERROR for any calculation error, IFNA for #N/A, and IF for a specific condition you can test.

A practical troubleshooting sequence

Click the cell and inspect the formula bar. Confirm that the cell contains a formula rather than text beginning with an apostrophe.
Temporarily examine the first argument on its own. Identify the exact result: an Excel error, an ordinary value, or a formula-entry problem.
Check the source cells and references. Confirm that referenced cells contain the expected values and that ranges include the intended rows.
Verify separators, quotation marks, parentheses, and the second argument. If Excel rejects the formula, consider your regional list separator.
Check calculation mode and recalculate if the displayed result may be stale. On Windows, F9 recalculates formulas; on Mac, use Fn+F9 where function-key settings require it, or use Formulas > Calculate Now.
Replace a broad fallback with a targeted one if it is hiding unrelated errors. Retest both a normal case and the error case before filling the formula down.

Testing both cases is important. A formula that returns a friendly message for a missing record may still have an incorrect range that fails to find records that are present. Try a key known to exist and one known to be absent, and confirm each result against the source data.

Frequently Asked Questions

Why does IFERROR still show #DIV/0!?
Check that the division is inside IFERROR’s first argument and that the displayed error comes from that same formula. If the cell is showing a stale result, check calculation mode. Also confirm that the visible cell is not a separate downstream formula producing its own error.

Why does IFERROR show the formula instead of a result?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Set the cell to General and re-enter the formula if it was stored as text; turn off Show Formulas if formulas are displayed across the worksheet.

Does IFERROR catch blank cells?
No. A blank reference is not by itself an Excel error, although using a blank in a calculation can produce a result such as zero. If blank inputs need special treatment, test for blanks explicitly or decide whether the resulting calculation is appropriate.

Should I use IFERROR or IFNA for a lookup?
Use IFNA when only a missing match, represented by #N/A, should receive a fallback. Use IFERROR when any error from the lookup expression should be replaced. IFNA is usually more diagnostic because it does not conceal unrelated errors such as an invalid reference.

Does IFERROR work in Excel for Mac and Excel for the web?
Yes. IFERROR is a standard Excel function available in current desktop and web versions. Ribbon placement, keyboard shortcuts, and regional argument separators can differ by platform or settings, but the function’s purpose is the same.

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.