Excel Formula to Return 0 When an Error Occurs in a Cell

Errors & fixes · Updated November 19, 2026

To return 0 when a formula produces an error in Excel, wrap the calculation in IFERROR. With the value to calculate in A2 and a divisor in B2, enter =IFERROR(A2/B2,0); Excel displays a numeric zero if the division returns an error and otherwise displays the calculated result.

The basic IFERROR formula

IFERROR checks a value or expression for an error and substitutes a result you specify if it finds one. Its syntax is IFERROR(value, value_if_error). The first argument is the calculation or reference to evaluate; the second is the result Excel should return if that argument evaluates to an error.

For a sales-per-unit calculation, suppose A2 contains total sales and B2 contains units sold. The formula =IFERROR(A2/B2,0) returns the quotient when the division works. If B2 is zero or blank, division returns #DIV/0!, so IFERROR returns 0 instead. If A2 contains 120 and B2 contains 4, the result is 30; if B2 contains 0, the result is 0.

The unquoted 0 in the formula is a number. That distinction matters if the result will be used in later calculations: a numeric zero can be added, averaged, or compared as a number. By contrast, "0" is text, even though it looks like a zero in the cell. Use the numeric form unless you specifically need text.

Which errors IFERROR can replace

IFERROR is not limited to division-by-zero errors. It replaces an error returned by the expression it evaluates, including common errors such as #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. Newer Excel features can also produce errors such as #SPILL! or #CALC!; IFERROR can handle errors returned by a formula using those features as well.

This broad coverage is convenient, but it can conceal different problems behind the same displayed result. For example, a missing lookup value and a deleted-cell reference may both become 0, even though they call for different fixes. IFERROR changes the result shown by the formula; it does not repair the underlying data, correct a broken reference, or remove an error from the source cell.

It also does not mean that every unexpected result becomes zero. If the expression returns a valid value such as an empty string, a negative number, or a text label, IFERROR passes that value through unchanged. The replacement is used only when the evaluated expression returns an Excel error.

How to add it to an existing formula

To apply IFERROR, keep the existing calculation intact as its first argument, add a comma, and put 0 as the second argument. For example, if a formula currently calculates a percentage as A2 divided by B2, wrap that expression rather than replacing it with a separate test.

  1. Select the cell containing the formula, or select the cell where you want the new formula.
  2. Type =IFERROR(, then enter or select the calculation.
  3. Type a comma followed by 0, then close the parenthesis.
  4. Press Enter. In Excel for Microsoft 365 on Windows, the formula bar and the cell both show the formula or its result as appropriate.

For example, with the lookup code in B2 and a two-column lookup table in D2:E100, where codes are in column D and descriptions are in column E, use =IFERROR(VLOOKUP(B2,$D$2:$E$100,2,FALSE),0). VLOOKUP searches for an exact match because its fourth argument is FALSE. If the code is absent, VLOOKUP returns #N/A and IFERROR displays 0. If a matching description exists, that description is returned instead.

To copy a formula down a column, use the fill handle or copy and paste it into the other rows. In the lookup example, the dollar signs keep the lookup table fixed as the formula is copied, while B2 changes to B3, B4, and so on. The formula’s error handling applies separately to each row.

Choose the right fallback value

Zero is useful when it represents a meaningful numeric result or when downstream calculations should treat an error as no amount. It is not always the clearest choice. A zero can be mistaken for a real measurement, a failed lookup, or a genuine absence of activity, so decide what the result is meant to communicate.

  • Use 0 when a numeric zero is an acceptable substitute and later formulas should be able to use it as a number.
  • Use an empty string when the cell should look blank. With a calculation in A2 divided by B2, for example, use =IFERROR(A2/B2,""). This returns text when there is an error, not a truly empty cell.
  • Use a message when readers need to know that a result is unavailable. With the lookup setup described above, =IFERROR(VLOOKUP(B2,$D$2:$E$100,2,FALSE),"Not found") displays an explanation instead of hiding the missing match.

Be especially cautious with averages and totals. A zero included in an average lowers that average, while a blank text result is generally ignored by functions such as AVERAGE when it is returned by a formula. In a sum, zero contributes nothing. Choose the fallback based on the meaning of the report, not just on which option makes the error marker disappear.

IFERROR versus IFNA and a direct test

IFNA is a narrower alternative: it replaces #N/A errors, which commonly occur when a lookup cannot find a match, but leaves other error types visible. With a lookup code in B2 and the same table in D2:E100, =IFNA(VLOOKUP(B2,$D$2:$E$100,2,FALSE),0) returns 0 for a missing match but will still show an error such as #REF! if the lookup formula has an invalid reference.

Use IFNA when a missing match is the one expected condition you want to handle and other errors should remain noticeable. Use IFERROR when you intentionally want the same fallback for any error produced by the wrapped expression. Leaving an unexpected error visible can make a damaged formula easier to diagnose.

A direct condition can be clearer when you want to handle one known cause rather than every error. With a numerator in A2 and a divisor in B2, =IF(B2=0,0,A2/B2) returns 0 when B2 is zero and otherwise performs the division. However, it does not catch other errors: if the division fails for another reason, that error remains. Also, a blank divisor behaves like zero in arithmetic, while text or invalid input may cause a different issue. This approach is best when the condition itself is meaningful and the other inputs are expected to be valid.

Common problems and troubleshooting

  • The error still appears: Check that the calculation is inside the first pair of IFERROR parentheses and that the fallback is the second argument. A missing closing parenthesis or misplaced comma can cause a formula-entry error rather than the intended result.
  • Excel shows a formula error instead of accepting the formula: Confirm that the formula starts with = and that the parentheses are balanced. This article’s formulas use comma argument separators, as is standard in many regional settings. Some Excel installations use semicolons as separators based on the computer’s regional settings; use the separator Excel expects if commas are rejected.
  • The cell looks like zero but behaves unexpectedly: Check whether the fallback is the number 0 or text "0". Also check the cell’s number format: formatting can change how a value appears without changing the formula’s underlying result.
  • A blank-looking cell is not truly empty: A formula returning "" still occupies the cell and returns a zero-length text value. Some functions and tests treat that differently from a cell that has never contained a value.
  • A real formula problem is being hidden: Temporarily remove IFERROR or inspect the wrapped calculation in a separate cell. This reveals whether the original issue is a missing lookup, invalid data, or a broken reference.

One subtle point is that the fallback expression can itself fail. IFERROR handles an error in its first argument, but if the second argument produces an error, that error can still appear. A simple numeric 0 is a safe fallback in this respect; more complex fallback calculations should be checked too.

When broad error handling is a poor fit

Wrapping every formula in IFERROR can make a worksheet look tidy while reducing its usefulness. In a working model, an error may be a signal that an input is missing, a reference moved, or a formula needs correction. Replacing all such signals with 0 can make totals or charts appear complete when they are not.

Consider a lookup report where a missing employee ID should be investigated. Returning 0 in a column of names can hide the fact that the source record was not found. A message such as “Not found” may be clearer, or IFNA may be appropriate if only an unmatched ID should be handled. In a calculation sheet, a zero denominator may be expected for some rows, but a text value in the same column may indicate a data-entry problem worth fixing rather than suppressing.

Use error handling at the point where you understand the expected failure. For a report intended for readers, a clear fallback can improve presentation. For a worksheet still being built or audited, leaving errors visible can be more useful. You can also add a separate status column or validation check so that a clean display does not erase the ability to identify problematic records.

Frequently Asked Questions

What is the Excel formula for 0 if error?
Use =IFERROR(your_formula,0), replacing your_formula with the calculation. For example, with a value in A2 and a divisor in B2, enter =IFERROR(A2/B2,0).

Does IFERROR return a number or text zero?
It returns a number when the fallback is unquoted 0. If you write "0", the fallback is text. Use numeric zero when the result needs to participate in arithmetic.

Can I show a blank instead of 0 when there is an error?
Yes. Put an empty string as the fallback, as in =IFERROR(A2/B2,"") when A2 contains the numerator and B2 the divisor. The cell will look blank, but it still contains a formula and returns text rather than being truly empty.

Should I use IFERROR or IFNA for a lookup?
Use IFNA if you only want to replace a missing-match #N/A result and want other errors to remain visible. Use IFERROR if you deliberately want the same fallback for any error returned by the lookup formula.

Does IFERROR fix the error in the source cell?
No. It changes the result of the formula containing IFERROR; the source value or separate cell that caused the error is not repaired. Investigate the underlying issue if the error indicates incorrect data or a broken formula.

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.