Why Is My IFERROR Formula Not Working in Microsoft Excel?

Your IFERROR formula usually appears not to work because Excel is not evaluating the formula you think it is, the error occurs outside IFERROR’s first argument, or the problem is not an error value IFERROR can catch. Check the formula structure, cell format, calculation mode, and argument separator first; then make sure the fallback value itself is valid.

What IFERROR does in Excel

IFERROR evaluates an expression and returns its result if that expression calculates successfully. If it produces an Excel error, IFERROR returns the fallback value you specify instead. That makes it useful for handling expected problems, such as dividing by zero or looking up a value that may not exist.

For example, with a quantity in A2 and a divisor in B2, if A2 contains 12 and B2 contains 0, =IFERROR(A2/B2,0) returns 0 instead of #DIV/0!. The zero is a replacement chosen for this example; it does not mean the actual calculation succeeded. Your fallback might instead be a message such as “Check the input,” depending on what makes sense for the worksheet.

IFERROR catches calculation errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NUM!, #NAME? and #NULL!. It does not repair the underlying cause. It simply substitutes the fallback, which can make a sheet easier to read but can also conceal a genuine problem if used indiscriminately.

Check the formula structure and argument placement

IFERROR has two arguments: the expression to evaluate, followed by the value to return if that expression gives an error. The entire calculation you want protected must be inside the first argument. A common mistake is to wrap only one cell or function, then continue the calculation outside IFERROR.

Suppose A2 contains an error and B2 is zero. With those cell values, =IFERROR(A2,0)/B2 first replaces the error in A2 with 0, then divides that result by B2. The division happens outside IFERROR, so the formula still returns #DIV/0!. If A2 contains #N/A and B2 is 2, that same formula returns 0, because the division succeeds.

To handle an error anywhere in the division, put the entire calculation inside the first argument. With A2 containing #N/A and B2 containing 0, =IFERROR(A2/B2,0) returns 0 because IFERROR catches the error produced while evaluating A2/B2. This illustrates why parentheses and the position of the closing parenthesis matter.

Check these details if the formula itself looks plausible:

There are two arguments, separated by the list separator your Excel installation expects.
The first argument includes the complete calculation or lookup you intend to protect.
The closing parenthesis comes after that complete expression and before the fallback argument’s separator.
The fallback is a value or expression that Excel can calculate without producing another error.

Know what IFERROR cannot catch

IFERROR handles errors produced while Excel evaluates its first argument. It does not catch every kind of “wrong result.” A blank cell, a zero, text returned by a formula, or a number that is mathematically unexpected is not automatically an error. For example, if a formula returns 0 when you expected 5, IFERROR has no reason to replace it unless the calculation actually generates an error.

It also cannot intercept a formula that Excel cannot parse. A missing parenthesis, an incomplete expression, or an invalid argument arrangement is a formula-entry or syntax problem: Excel cannot evaluate the formula far enough to apply IFERROR. Correct the formula structure first. Similarly, if a cell displays the formula as text rather than calculating it, IFERROR is not running at all.

Some errors may be generated in a different part of a larger worksheet formula than you expect. For example, an error in a later operation outside the protected expression will remain visible. Trace the calculation from the inside out and identify which exact part returns the error, rather than assuming that placing IFERROR somewhere in the formula will cover everything.

Common reasons the formula still shows an error

A particularly confusing case is when the fallback value also fails. IFERROR can replace an error in its first argument, but it cannot turn an invalid fallback into a successful result. If the fallback is a calculation that divides by zero, references a deleted cell, or contains another error, you may still see an error. Use a simple fallback temporarily, such as 0 or a text message, to determine whether the original fallback is the source of the problem.

Another frequent cause is a wrong separator. In many English-language Excel installations, arguments are separated with commas, as in =IFERROR(A2/B2,0). Excel follows regional settings, however, and some configurations use semicolons as the list separator. If Excel rejects a formula with commas, check your regional list separator or use the separator Excel inserts when you select function arguments. Do not change separators at random: use the one required by your installation.

Also check that the function name is spelled IFERROR and that the formula begins with an equals sign. A typo in the function name can produce #NAME?, and a cell reference that no longer points to a valid location can produce #REF!. IFERROR can catch those errors when they occur inside its first argument, but it will not help if the formula is too malformed to be evaluated.

Finally, consider whether another part of the workbook is showing a different cell’s error. Select the cell displaying the problem and inspect its formula bar. A nearby cell, a dependent formula, or a conditional display can make it easy to misidentify which formula is failing.

Choose a fallback that does not hide useful information

The fallback should communicate what the worksheet user needs to know. Returning 0 can be appropriate when zero is a meaningful default, but it can be misleading if it looks like a real measured or calculated value. A message such as “Not found” may be clearer for a missing lookup result. A blank string can make a report look cleaner, but it can also hide missing data.

For example, with a lookup key in E2, keys in A2:A10, and corresponding results in B2:B10, =IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found") returns “Not found” if the lookup produces an error, including when the key is absent. The fourth VLOOKUP argument is FALSE so the lookup requests an exact match. This formula also masks other errors from the lookup expression, though, so “Not found” could appear even when the real issue is a bad reference or data problem.

When you want to handle only a missing lookup match, IFNA may be more precise than IFERROR. IFNA substitutes a fallback for #N/A but leaves other errors visible. That distinction can help expose a broken reference or incompatible value instead of disguising it as an ordinary missing result. Use IFERROR when you genuinely want to handle any error produced by the protected expression.

Check whether Excel is calculating the formula

If you see the literal formula in the cell instead of its result, the cell may be formatted as Text, or the formula may have been entered with a leading apostrophe. Select the cell, change its number format to General from Home > Number Format, then re-enter the formula. Merely changing the format may not convert an existing text entry; pressing F2 and then Enter, or retyping the formula, prompts Excel to interpret it again.

Another possibility is that Show Formulas is enabled. In Excel for Microsoft 365 on Windows, use Formulas > Show Formulas to toggle the display, or press Ctrl+` (the grave accent key; keyboard layout can affect its position). When this view is active, Excel displays formulas in cells instead of their calculated results. On Mac, the command is also available on the Formulas tab; the shortcut can differ with keyboard layout.

If the formula evaluates but shows an old result, verify calculation settings. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options > Automatic. With manual calculation selected, formulas may not recalculate when source values change; press F9 to recalculate the workbook. Excel for Mac has calculation settings as well, though the location can differ by release. Excel for the web generally recalculates supported formulas automatically.

A practical troubleshooting sequence

Work through the checks in order so you can isolate the cause rather than masking it:

Select the cell and inspect the formula bar. Confirm you are looking at the formula that produces the displayed problem.
Check that the cell contains a formula, not text, and that Show Formulas is not enabled.
Confirm that the expression you want protected is entirely inside IFERROR’s first argument.
Temporarily replace the fallback with a simple value, then see whether the original error disappears.
Check the formula’s references and test the inner calculation separately in an empty cell, if useful.
If Excel rejects the formula, check the function spelling, parentheses, and regional argument separator.
If results are stale, check calculation mode and recalculate.

Testing the inner expression is often the fastest way to understand the result. If the expression itself returns a number or text, IFERROR will return that same result. If it returns an error, IFERROR should substitute its fallback, provided the formula is valid and the fallback works.

IFERROR compared with alternatives

Choose the error-handling function that matches the situation. IFERROR is broad: it replaces any supported error from its first argument. IFNA is narrower and targets #N/A, commonly associated with missing lookup matches. If you want to prevent a known error rather than hide it afterward, an explicit condition can be clearer—for instance, checking whether a denominator is zero before dividing.

That kind of condition is useful when a zero denominator has a specific meaning in your data, or when you want a different response for zero than for other errors. It does require you to decide what counts as an invalid input. IFERROR is simpler when several possible calculation errors should share the same fallback. Whichever approach you use, document unusual fallback choices so the next person reading the sheet understands whether a blank, zero, or message represents a genuine result or a handled error.

IFERROR is available in modern Excel, including Microsoft 365. If you are sharing a workbook with very old Excel releases, compatibility may matter; IFERROR was introduced in Excel 2007. For current Microsoft 365 desktop and web versions, the function itself is ordinarily supported. When a workbook is used in other spreadsheet programs, check that program’s function compatibility and separator conventions.

Frequently Asked Questions

Why does IFERROR return 0 instead of a blank?
It returns the fallback you supplied. If the second argument is 0, the result is zero; if you want an empty-looking result, the fallback can be an empty text string, written as two quotation marks. Remember that a blank-looking result may make missing or invalid data less visible.

Why does IFERROR show the original error?
Check whether the error occurs outside the first argument, whether the formula is malformed or stored as text, and whether you are inspecting the correct cell. Also verify that the fallback itself does not produce an error. IFERROR cannot handle a formula that Excel cannot parse.

Does IFERROR catch #N/A?
Yes. #N/A is one of the errors IFERROR catches when it is produced by the first argument. If you want to replace only #N/A and leave errors such as #REF! visible, consider IFNA instead.

Why does Excel say there is a problem with the formula?
That usually points to an entry or parsing issue rather than an error result. Check the parentheses, the function spelling, and whether your Excel settings require semicolons instead of commas between arguments. A formula error that Excel cannot parse is not something IFERROR can trap.

Can IFERROR hide a real problem in my workbook?
Yes. It can replace errors caused by missing data, damaged references, or incorrect calculations with an ordinary-looking fallback. Use a specific message or a narrower function such as IFNA when appropriate, and test the underlying expression if a result seems unexpected.

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.