Why Is IFERROR Not Working in Excel?

IFERROR works only when the expression inside it returns an Excel error; it will not replace ordinary text, blanks, or unexpected-but-valid results. If you still see an error, check that the entire failing expression is inside IFERROR, the formula is entered as a formula, and the function’s arguments use the separator your Excel settings expect.

What IFERROR does—and what it does not do

IFERROR evaluates a value or formula and returns an alternative value if that evaluation produces an error. The function’s structure is =IFERROR(value, value_if_error). The first argument is the calculation or reference to check; the second is what to show if that calculation fails. It can handle errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. In current Microsoft 365 Excel, it can also handle errors such as #SPILL! when they occur in the expression being evaluated.

IFERROR does not decide whether a result is sensible. A formula that returns zero, an empty cell, the text “Not found,” or an incorrect number has not necessarily produced an Excel error, so IFERROR leaves that result alone. It also does not repair the underlying problem: it replaces the visible error with your chosen fallback. That may be useful for a report, but it can hide a broken reference or bad input that needs correction.

For example, with 12 in A2 and 0 in B2, the calculation A2 divided by B2 produces #DIV/0!. With those values in A2 and B2, =IFERROR(A2/B2,"Not available") displays Not available. The fallback is text, not a correction to the division; changing B2 to a nonzero value lets the calculation return a number instead.

Check whether the result is actually an error

A common source of confusion is expecting IFERROR to catch something that only looks wrong. If a lookup returns an empty cell or a formula returns an empty string, that is not the same as an error. Likewise, a number stored as text can cause some calculations to behave unexpectedly without every such case producing an error. First inspect the exact cell result and the formula bar, rather than relying only on how the cell is formatted.

If the cell displays a value such as 0 or blank, IFERROR has no error to replace.
If it displays text such as “#N/A” but the text was typed or imported, that text is not the #N/A error value.
If the formula returns an error in one row but not another, compare the inputs and references in both rows.
If a cell appears blank, check whether it is truly empty, contains a formula returning an empty string, or uses formatting that hides its contents.

For a concrete blank-result example, assume A2 contains a value and B2 is empty. With those cells as the inputs, =IFERROR(A2/B2,"") displays an empty string if Excel treats the division as a divide-by-zero error. This can make a worksheet look cleaner, but the cell is not necessarily truly empty: it still contains a formula, which matters for counting, charts, and later calculations.

Make sure the whole calculation is inside IFERROR

IFERROR can only catch errors generated by its first argument. If you put it around one part of a larger calculation, an error in the part left outside the function can still appear. Parentheses make the scope clear: decide which complete calculation should be treated as one unit, and wrap that expression rather than only one component.

For example, suppose A2 contains 10, B2 contains 0, and C2 contains the text x. With those values in A2:C2, =IFERROR(A2/B2,0)+C2 still returns #VALUE!. IFERROR replaces the division error with 0, but then Excel tries to add the text in C2 to that result. With the same cells and values, =IFERROR(A2/B2+C2,0) puts the entire addition inside the function, so it returns 0 if any part of that expression errors. That may be appropriate, but it can also conceal a bad value in C2; use a fallback that makes sense for the worksheet.

Check nested formulas and references, too. A formula may refer to a cell that already contains an error, or an error may arise in an argument of another function. Trace the calculation from the inside out. In Excel for Microsoft 365 on Windows, select the formula cell and use Formulas > Evaluate Formula to step through the calculation. This can reveal which part produces the error before IFERROR handles it—or where an unwrapped error enters afterward.

Look for formula-entry and separator problems

If Excel reports a problem with the formula itself, IFERROR cannot catch that syntax problem. IFERROR needs two arguments, separated by the list separator used by your regional settings. In many installations that separator is a comma; in some locales, Excel expects a semicolon. Use the separator Excel accepts when entering formulas on your computer.

Also check for a missing parenthesis, a misspelled function name, or quotation marks that do not come in pairs around a text fallback. A formula such as =IFERROR(A2/B2) is incomplete because the fallback argument is missing. That is different from a valid IFERROR formula whose first argument evaluates to an error. Correct the formula structure first, then test the calculation.

Another common problem is that the formula is stored as text, so Excel displays the characters instead of calculating them. Check whether the cell begins with an apostrophe, whether its number format is Text, or whether Show Formulas is turned on. On Windows, Ctrl+` toggles formula display; the backtick key is usually near the top-left of the keyboard. If a formula is text, set the cell to General, then edit and confirm it again. Simply changing the number format may not convert an existing text entry automatically.

Choose a fallback that helps rather than hides the issue

The second argument is a design choice. A blank string can keep a table visually tidy, a clear label can tell readers why a result is missing, and zero can be suitable when zero has a real meaning in the calculation. These are not interchangeable. For instance, using zero for a missing lookup can make totals or averages misleading, while showing an error may be preferable during analysis.

With a numerator of 12 in A2 and a zero denominator in B2, =IFERROR(A2/B2,0) returns 0. That output might be acceptable for a particular summary, but it can also imply that the true ratio is zero. A label such as “Check denominator” communicates the problem more honestly. If the error should be fixed rather than hidden, leave the error visible while you investigate the inputs or references.

For lookup formulas, consider whether every possible error should receive the same fallback. IFERROR catches any error in its first argument, not only “item not found.” If the lookup has a broken range reference or an invalid argument, returning “Not found” can misdiagnose the cause. In Microsoft 365, XLOOKUP can provide a not-found result directly, but that option addresses a missing match and does not make unrelated formula errors harmless. Use broad error handling only when you intend to handle all errors from that expression.

Use a short troubleshooting sequence

Work through these checks in order. This helps distinguish a formula that is not evaluating from a formula that is evaluating but returning a different result than you expected.

Read the exact displayed result. Confirm it is an Excel error value, not text, zero, or a blank-looking result.
Click the cell and inspect the formula bar. Confirm the entry starts with an equals sign and that IFERROR has both arguments.
Identify the complete expression that can fail. Make sure it is inside the first argument, including any later arithmetic that could also produce an error.
Temporarily inspect or evaluate the inner calculation so you can identify the source error. Correct invalid inputs or references where possible.
Check quotation marks, parentheses, and the comma or semicolon separator accepted by your Excel settings.
If formulas are not recalculating, check Formulas > Calculation Options > Automatic. In Manual calculation mode, a formula may keep showing an old result until recalculation.
Choose a fallback that communicates what the result means, then test the formula with both valid inputs and a case that should trigger the fallback.

For a simple test, with 12 in A2 and 3 in B2, =IFERROR(A2/B2,"Not available") returns 4. Change B2 to 0 and it returns Not available. If the first test does not show 4, the issue is likely formula entry, cell content, or calculation—not error handling. If the first test works but your real formula does not, compare the real formula’s structure and the exact error source.

When IFERROR is not the best tool

Use IFERROR when you deliberately want one result for any error from a calculation. If you only want to handle a specific condition, a more targeted formula can make the logic clearer. For example, IF can test whether a denominator is zero before dividing; that avoids generating a divide-by-zero error in the first place. It does not automatically address other possible errors, such as invalid text in the numerator, so decide which cases matter.

With the numerator in A2 and denominator in B2, =IF(B2=0,"No denominator",A2/B2) displays No denominator when B2 is zero and otherwise performs the division. If A2 contains unsuitable text, the division can still return an error, which is often useful because it exposes a separate data problem. The choice is whether to handle every error uniformly with IFERROR or test a known condition explicitly and let unexpected errors remain visible.

For troubleshooting, avoid wrapping a whole workbook in layers of IFERROR just to make errors disappear. A wide blanket can turn a misspelled reference, an unexpected data type, or an accidental deletion into a plausible-looking blank or zero. Prefer a clear fallback at the point where the error is expected, and document what that fallback means if other people use the sheet.

Frequently Asked Questions

Why does IFERROR still show #VALUE!?
Check whether the #VALUE! is produced inside the first argument. If it occurs later in the formula, outside IFERROR, it will remain visible. Also verify that the displayed entry is the formula you intended and not an incomplete or text-formatted formula.

Does IFERROR catch #N/A?
Yes. If the first argument evaluates to the actual #N/A error, IFERROR returns its second argument. It does not treat the text string “#N/A” as an error, and it does not tell you why a lookup or other calculation produced #N/A.

Why is my IFERROR result blank?
If the second argument is an empty string, written as two quotation marks, IFERROR intentionally displays a blank-looking result when the first argument errors. If you expected a message or number, check the second argument and confirm the formula being evaluated is the one in the cell.

Can IFERROR hide errors in a formula?
Yes. That is its purpose, but it can hide errors that point to a genuine problem. A fallback of zero or blank may make a report look complete even when source data or references are wrong, so use a meaningful label or leave unexpected errors visible while investigating.

Why does Excel reject my IFERROR formula?
Check that it has two arguments, balanced parentheses, paired quotation marks around text, and the separator your regional settings require. If Excel accepts the formula but shows it literally, check for a leading apostrophe, Text formatting, or Show Formulas mode.

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.