Excel Formula to Return 0 If There’s an Error

Errors & fixes · Updated November 19, 2026

To make an Excel formula return 0 whenever it produces an error, wrap it in IFERROR. If the value to calculate is in A2 and the divisor is in B2, use =IFERROR(A2/B2,0); it returns the division result when successful and 0 if the calculation produces an error.

The quick answer: use IFERROR

IFERROR checks the result of an expression or formula. It returns that result if it is valid, and returns a fallback value that you specify if Excel detects an error. The fallback in the example above is the number 0. Put the formula you want to protect in the first argument, then put 0 in the second argument.

For another illustration, with a calculation in C2 that you want to leave intact, place this in D2: =IFERROR(C2,0). The assumed layout is that C2 contains the result or error to handle; D2 displays the handled result. If C2 contains an ordinary number, D2 shows that number. If C2 contains an error such as #N/A or #DIV/0!, D2 shows 0.

You can wrap a larger expression directly, rather than referring to a separate cell. For example, with a quantity in A2 and a unit price in B2, =IFERROR(A2*B2,0) returns the product unless the multiplication evaluates to an error. The layout assumption is A2 for quantity and B2 for price. If both contain valid numbers, Excel calculates their product; the fallback is used only for an error, not just because a cell is empty.

How IFERROR works

The function has two arguments: IFERROR(value,value_if_error). The first is the calculation or value Excel should evaluate. The second is what to display if that evaluation returns an error. That second argument can be a number, text, a cell reference, or another formula; using 0 makes the result numeric zero.

IFERROR handles Excel error values including #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. It can be used around a simple operation, a function, or a more complex expression. If an expression contains several operations, wrapping the whole expression means any error that arises anywhere inside it can trigger the same fallback.

It is important that “error” has a precise meaning here. A blank cell, a legitimate zero, or text that a formula accepts is not automatically an Excel error. IFERROR does not replace every unexpected or undesirable result; it responds when the evaluated value is an actual Excel error.

Examples for common worksheet calculations

A frequent use is preventing a division-by-zero error. With sales in A2 and units in B2, enter this formula in C2: =IFERROR(A2/B2,0). If A2 is 240 and B2 is 12, C2 returns 20. If B2 is 0, the division would produce #DIV/0!, so C2 returns 0 instead.

This can also protect a ratio or average calculation. With numeric scores in B2:B10, use =IFERROR(SUM(B2:B10)/COUNT(B2:B10),0). The assumed layout is that B2:B10 holds the scores and the formula is entered in another cell. If the range contains scores, the result is their arithmetic mean; if it contains no numeric values, COUNT returns 0 and the division would fail, so the formula returns 0. Check that this is the intended meaning: an empty set of scores and an actual average of zero are different situations.

IFERROR also works with functions that can fail for reasons beyond arithmetic. With a lookup value in E2, a table in A2:C100, and the desired return values in column C, use =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),0). Enter it outside the table. If E2 matches a value in the first column of the table, Excel returns the corresponding value from column C. If there is no match, or another error occurs, the displayed result is 0. The dollar signs keep the table range fixed if you copy the formula down.

Choosing between IFERROR and IFNA

IFERROR is broad: it catches any error returned by the calculation. That convenience is useful when every error should have the same fallback, but it can also hide a problem you would rather find. For example, a misspelled function name, a broken cell reference, or a text value where a number was expected may all be replaced by 0, making the worksheet look normal when it is not.

If the only expected problem is a lookup value not being found, consider IFNA. With the lookup value in E2, lookup keys in A2:A100, and matching results in C2:C100, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),0). The formula returns 0 for #N/A, commonly produced by a missing exact-match lookup, but it leaves other errors visible. This makes it easier to notice issues such as an invalid reference instead of silently treating them as a missing match.

Use IFERROR when the instruction really is “show zero for any error.” Prefer IFNA when only “not found” is an expected condition and other failures should remain visible. You can also correct the underlying data or calculation instead of suppressing the error. For instance, if division by zero represents a special case rather than a general error, an explicit condition may communicate the logic more clearly.

When zero is not the best fallback

Zero is a valid numeric result, so it can be misleading when it stands in for a failed calculation. In a chart, a zero may appear as a real data point. In a total or average, it may affect downstream calculations. In a report, it may look like a measured value rather than a missing or invalid one. Decide what the fallback should mean to the people who will read or use the workbook.

  • Use 0 when a failed calculation should count as zero for the next calculation, and that interpretation is correct.
  • Use a blank-looking result when you want to avoid displaying a number. With a value in C2 that may contain an error, enter =IFERROR(C2,"") in D2. The assumed layout is C2 as the value being checked and D2 as the display cell. The result is an empty text string for an error, not a truly empty cell.
  • Use a message when readers should know that the result could not be calculated. With a calculation in C2, enter =IFERROR(C2,"Check input") in D2. D2 displays the message for an error and otherwise shows the result from C2.
  • Leave the error visible when it signals a mistake that needs investigation, especially in a calculation used for auditing or decision-making.

A blank-looking text result is not interchangeable with numeric zero. It may be ignored by some calculations that use numeric values, but it can affect formulas and sorting differently from a genuinely empty cell. Likewise, a text message in a result column can prevent that column from being treated as entirely numeric. Choose a fallback that suits what happens next in the workbook, not only what looks tidy on screen.

Common mistakes and troubleshooting

  • Wrapping the wrong part: Put the calculation that can fail inside IFERROR. If the formula only refers to an unrelated cell, it will not protect the calculation you meant to handle.
  • Expecting blank cells to trigger the fallback: A blank may be treated as zero in some arithmetic, or may simply produce a valid result. IFERROR reacts to errors, not to blanks as such.
  • Hiding an error that should be fixed: If every output suddenly becomes zero, inspect the underlying formula and source data. The fallback might be concealing a broken reference, a mismatch in lookup values, or text stored where a number is expected.
  • Using text instead of numeric zero: The fallback 0 is a number. A quoted zero, "0", is text and can behave differently in later calculations.
  • Missing a parenthesis: The expression and both IFERROR arguments need to be enclosed correctly. If Excel reports a formula problem, check the opening and closing parentheses and the separators.

The formulas in this page use commas between arguments, as is standard in many Microsoft 365 installations. Excel’s list separator can depend on regional settings. If Excel rejects a formula with commas, check the separator expected by your installation; in some locales, a semicolon is used instead. This changes the separator, not the function’s logic.

Alternatives when you want to prevent a specific error

Rather than catch every error after it happens, you can test for a known condition first. With the numerator in A2 and divisor in B2, enter =IF(B2=0,0,A2/B2) in C2. If B2 is zero, C2 returns 0; otherwise it divides A2 by B2. This makes the zero-divisor rule explicit, but it does not catch other errors, such as text in A2 that cannot be used in the division.

That distinction is useful when errors should not all receive the same treatment. A formula that checks a condition can preserve visibility of unexpected failures while handling a predictable case. On the other hand, it can be longer and may need additional conditions if several cases are expected. Use IFERROR for a general fallback, IFNA for a missing-match fallback, or a condition test when you know exactly what case you want to handle.

In Microsoft 365 for Windows, enter the formula in the cell where you want the result and press Enter. Excel for the web supports IFERROR as well. The formula’s behavior is the same across these versions; differences in the ribbon are not relevant to entering this function. On Mac, enter the same formula in a cell and confirm it in the usual way for your Excel setup.

Frequently Asked Questions

What formula returns 0 instead of #DIV/0!?
With the numerator in A2 and divisor in B2, enter =IFERROR(A2/B2,0). It displays 0 if the division returns an error, including when B2 is zero.

Does IFERROR catch every Excel error?
It catches Excel error values returned by the value being evaluated, including common errors such as #N/A, #VALUE!, #REF!, and #DIV/0!. It does not automatically replace ordinary blanks, zero values, or every result that seems incorrect.

How do I return a blank instead of 0 when a formula has an error?
With the calculation or value in C2, enter =IFERROR(C2,"") in the result cell. The second argument is an empty text string, which looks blank but is not the same as an actually empty cell.

Should I use IFERROR or IFNA for a lookup?
Use IFNA if a missing match is the only expected error and you want other errors to remain visible. Use IFERROR if you deliberately want every error from the lookup formula to produce the same fallback.

Why does my formula show 0 even though I expected a result?
IFERROR may be hiding an error in the calculation. Temporarily remove the wrapper or inspect the original expression and its referenced cells. Check for invalid references, unexpected text, incorrect lookup ranges, and values that do not match as expected.

Related

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.