Excel Formula Error Handling: Fix and Prevent Errors

In Excel, the quickest way to handle a formula error is to wrap the calculation in IFERROR, which returns a chosen value if the calculation produces an error. For example, if A2 contains revenue and B2 contains units, =IFERROR(A2/B2,"") displays a blank instead of a division error when B2 is zero; with A2 equal to 120 and B2 equal to 6, it returns 20. Use a specific message or a more targeted check when hiding every kind of error would conceal a problem you need to fix.

What Excel formula errors mean

An error value is Excel’s way of reporting that a formula cannot produce its expected result. The code often points to the cause, so it is worth reading before adding a workaround. Common errors include:

#DIV/0! means a formula divided by zero or by a blank cell treated as zero.
#N/A commonly means a lookup could not find a matching value, though formulas can also return it deliberately.
#VALUE! often indicates that a formula received a value of the wrong type, such as text where a number was expected.
#REF! means a formula refers to an invalid cell reference, often because a referenced row, column, or cell was deleted.
#NAME? can indicate a misspelled function or an unrecognized name in the formula.
#NUM! indicates a numeric problem, such as an invalid number or a calculation outside Excel’s supported range.
#SPILL! appears when a dynamic-array formula cannot place its results because its spill range is blocked.

Other errors exist, including #NULL! and #CALC!. The details depend on the formula and Excel feature involved. Treat the displayed error as a clue, not just something to cover up: correcting a misspelled function or a deleted reference is usually better than replacing the result with zero.

How IFERROR works

IFERROR evaluates an expression and returns that expression’s result if it is valid. If the expression produces an error, it returns the fallback value you specify instead. The fallback can be text, a number, a blank-looking text value, or another calculation. In Microsoft 365 for Windows, you can enter it directly in a cell or use it inside a larger formula.

For a concrete division example, assume A2 contains 120 and B2 contains 0. With revenue in A2 and units in B2, =IFERROR(A2/B2,"Check units") returns the text Check units. If B2 is changed to 6, the same formula returns 20. This is more informative than an empty display when a zero denominator needs investigation.

IFERROR catches any error produced by the expression it wraps, not just the error you had in mind. That is useful for a report where an error would be confusing, but it can also hide an unrelated #REF! or #VALUE! error. A formula that was broken for a different reason may appear to be working because its error has been replaced with a plausible-looking result.

A blank-looking fallback also is not the same as an empty cell. With amounts in A2 and a divisor in B2, =IFERROR(A2/B2,"") returns a zero-length text value when division fails. The cell looks blank, but it contains a formula; functions such as COUNTA can count it, and it is not equivalent to a truly empty cell. Use a visible label if people need to distinguish missing data from a result intentionally left blank.

Choose IFERROR or IFNA

Use IFERROR when any error from a calculation should receive the same fallback. Use IFNA when you want to handle only #N/A and allow other errors to remain visible. This distinction is particularly useful with lookups: “not found” may be an expected outcome, while a broken reference or an invalid calculation deserves attention.

Assume product codes are in A2:A100, descriptions are in C2:C100, and the code to find is in E2. With that layout, =IFNA(VLOOKUP(E2,A2:C100,3,FALSE),"Not found") returns the matching description or Not found if VLOOKUP returns #N/A. If the formula instead has an invalid column index or a broken reference, its other error is not silently converted to that message.

IFERROR and IFNA are available in current Microsoft 365 Excel. IFERROR was introduced in Excel 2007, and IFNA is available in Excel 2013 and later. For very old Excel versions, a formula may need older functions or a different structure; check the version used by everyone who will open the workbook.

Prevent an error instead of masking it

If the likely cause is clear, a condition can prevent the failing operation. For a denominator in B2 and numerator in A2, =IF(B2=0,"",A2/B2) returns a blank when B2 is zero and returns the quotient otherwise. With A2 equal to 120 and B2 equal to 6, the result is 20; with B2 equal to 0, the result is blank. This approach makes the rule explicit, but it does not handle every possible problem: text in A2, for example, could still cause an error.

For a lookup, decide whether a missing match is normal. If it is, return a clear message with IFNA. If a missing match signals a data-quality issue, consider leaving the error visible or reporting the missing key in a review column. A displayed error can help the person responsible for the data find a mismatch that a blank or zero would conceal.

Use checks that match the business meaning of the data. A zero quantity may mean “not applicable,” while a blank quantity may mean “not entered yet”; treating both as the same can make a report misleading. Before choosing a fallback, ask what a user should infer from it and whether downstream calculations should include it.

Diagnose errors before changing formulas

When a formula returns an error, inspect its inputs and references first. In Excel for Windows, select the cell and use Formulas > Error Checking for available guidance. Formulas > Evaluate Formula can step through a formula to show how Excel evaluates its parts. The exact prompts depend on the error, and these tools help diagnose a formula rather than automatically decide the right business rule.

Check the cells named in the formula. Confirm that values that should be numbers are not stored as text, and look for blanks or unexpected spaces.
For #DIV/0!, inspect the denominator and decide whether zero is valid input, missing input, or a data-entry mistake.
For #N/A, compare lookup values carefully. Extra spaces, different data types, or a genuinely absent key can prevent a match.
For #REF!, inspect the formula for deleted or moved references and restore or rebuild the intended reference.
For #SPILL!, check whether cells in the expected output area contain data or otherwise block the array result.

When you need to keep an error visible in one cell but label it elsewhere, use a test function. Assume A2 contains a calculation result; =IF(ISERROR(A2),"Review formula","OK") displays Review formula if A2 contains any error and OK otherwise. ISERROR tests an existing value; it does not repair the calculation in A2. For a lookup that needs only a not-found test, ISNA is narrower, while ISERR tests errors other than #N/A.

Choose a useful fallback value

A fallback is part of the workbook’s meaning, not merely a formatting choice. Common options include a short status message, a deliberately chosen number, or a blank-looking result. Select one that will not be mistaken for a valid measurement:

Use a message such as Not found or Check input when someone needs to take action.
Use a blank-looking result when showing an error would disrupt a presentation, but document what the blank means and remember it is still a formula result.
Use zero only when zero is the correct interpretation. Replacing a missing price or failed calculation with zero can distort totals, averages, charts, and decisions.

For example, if a failed sales lookup is replaced with zero, a later total may understate sales without revealing that a product code was missing. A label makes the issue easier to notice, although text in a numeric column can affect later calculations and charting. If the output must remain numeric, consider recording the error status in a separate column rather than silently converting it to a number.

Common mistakes and compatibility

The most common mistake is wrapping a long formula in IFERROR before confirming that its logic is sound. If the fallback is an empty string, every error may look like an intentionally blank result. That can make a workbook appear tidy while hiding misspelled names, invalid references, or wrong input types. During development, leave errors visible or use a descriptive fallback; apply a quieter display only when the underlying cases are understood.

Another mistake is assuming a fallback fixes the source data. It does not: IFERROR changes what the formula displays, not the value in the source cells or the reason the calculation failed. If the workbook is shared, explain what messages mean and which errors require correction. Microsoft 365 on Mac and Excel for the web also support the core functions described here, though ribbon arrangement and some interface details can differ from Windows desktop Excel.

For a workbook opened in older Excel versions, verify that the functions used are supported there. IFERROR and IFNA are established worksheet functions, but newer formula features elsewhere in a workbook may not behave the same in an older release. When sharing across versions, test a copy in the oldest version that must be supported and make sure fallback text, blank-looking values, and downstream totals still make sense.

Frequently Asked Questions

What is the formula to hide errors in Excel?
Wrap the calculation in IFERROR and provide a fallback. For a formula in A2 that you want to display as blank if it errors, use =IFERROR(A2,""). This hides every error from A2, so use a message or a narrower check if you need to know why it failed.

What is the difference between IFERROR and IFNA?
IFERROR handles all Excel error values produced by the expression. IFNA handles only #N/A. For a lookup, IFNA can display a not-found message while still exposing other problems, such as a broken reference.

Can IFERROR return zero instead of an error?
Yes. Give zero as the fallback, but do so only when zero is a valid interpretation of failure. Otherwise, totals or averages can treat an unsuccessful calculation as a real zero and mislead readers.

Does a blank returned by IFERROR count as an empty cell?
No. An empty-string result looks blank but remains the result of a formula. It is not identical to a cell with no content, and some functions, including COUNTA, can count it.

Why does my formula still show an error after I use IFERROR?
Check that IFERROR wraps the part of the calculation that actually produces the error, that its parentheses are balanced, and that the fallback is correctly entered. Also check for errors outside the wrapped expression, such as a problem in a separate referenced formula or an invalid formula structure.

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.