IFERROR to Return Zero in Excel: Formula and Uses

To make an Excel formula return zero when it produces an error, wrap it in IFERROR. For example, with revenue in A2 and units in B2, enter =IFERROR(A2/B2,0); if the division fails, Excel displays 0 instead of the error.

What IFERROR Does

IFERROR checks the result of an expression. If that result is a value Excel can calculate, it returns the value; if the result is an error, it returns the fallback you specify. In the example above, a nonzero unit count gives the ordinary revenue-per-unit result, while dividing by zero produces a zero.

The function takes two arguments: the expression to evaluate and the value to return if that expression generates an error. The fallback can be a number, text, a cell reference, or another calculation. Using zero as the fallback is common in reports where a zero is a useful display value, but it is a choice about presentation—not a repair to the calculation that caused the error.

IFERROR is available in modern Excel, including Microsoft 365. It can catch errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NUM!, #NAME?, and #NULL!, as well as errors produced by newer dynamic-array calculations. It does not treat every unusual-looking result as an error: a blank, a text value, or the number zero is still a valid result unless the formula itself produces an Excel error.

How to Use It in a Calculation

Put the existing calculation first and the fallback second. With the same revenue and units layout—revenue in A2 and units in B2—the formula =IFERROR(A2/B2,0) returns 5 when A2 is 15 and B2 is 3. If B2 is 0, the division would normally produce #DIV/0!, so IFERROR returns 0 instead.

This pattern works for many kinds of calculations, not just division. For instance, a report might calculate a margin, rate, or ratio that is undefined for some rows. If zero is the intended reporting value for those rows, IFERROR can keep the result column free of visible error messages. Before applying it down a column, check that zero means something sensible to the people using the report.

To enter the formula, select the result cell, type it in the formula bar or directly into the cell, and press Enter. Use the fill handle to copy it to other rows. When copied down, relative references such as A2 and B2 adjust to A3 and B3. If your source cells are in an Excel Table, structured references can make formulas easier to read, though the underlying IFERROR behavior is the same.

Choosing Zero, Blank, or Another Fallback

Zero is not always the right fallback. A zero can be read as a measured result, even when the true meaning is that no result could be calculated. In a financial or operational report, that distinction can affect totals, averages, charts, and decisions. Choose a fallback that communicates what happened rather than merely making the sheet look tidy.

Use 0 when a missing or invalid calculation should count as zero for the report, and users understand that convention.
Use a short label such as Not found when the calculation is a lookup and the missing match matters.
Use an empty string when you want the cell to appear blank, while remembering that the formula still occupies the cell.
Leave the error visible during development when it signals a problem that should be corrected rather than hidden.

For example, with amounts in A2 and a divisor in B2, this version returns a blank if B2 is blank, but returns zero for a calculation error when B2 is not blank: =IF(B2="","",IFERROR(A2/B2,0)). The assumed layout is amounts in A2 and the divisor in B2. This is useful when an empty row should remain visually empty. It is different from returning zero for every error, so decide whether a truly blank divisor should be treated as missing data or as a zero-result case.

Blank Cells and Empty Text

A frequent source of confusion is that an empty-looking cell is not necessarily an error. A cell may be genuinely empty, or it may contain a formula that returns an empty string. IFERROR only substitutes its fallback when the evaluated expression produces an error. If a formula returns empty text successfully, IFERROR leaves that result alone.

Similarly, if a source cell contains text where a numeric calculation expects a number, the calculation may return #VALUE! and IFERROR will use the fallback. That can hide an input-quality issue. If the text is an accidental entry such as a space or a word in a numeric column, replacing the error with zero may make the report misleading. Validate or clean the source data when the input itself is what needs attention.

If you need different handling for blanks, text, and errors, make those rules explicit. A nested IF can test for a blank before the calculation, while IFERROR handles errors that remain. Avoid adding layers of conditions without a clear requirement: complex formulas can be harder to audit than leaving an error visible or correcting the source data.

IFERROR Compared with IFNA

IFERROR catches all Excel error types. IFNA is narrower: it substitutes a fallback only when the result is #N/A. That difference matters in lookup formulas. If a lookup can legitimately fail to find a match, but other errors should remain visible, IFNA can preserve useful warnings rather than masking them.

With lookup values in E2 and E3, a search list in A2:A100, and corresponding results in B2:B100, use =IFNA(XLOOKUP(E2,A2:A100,B2:B100),0) to return zero when XLOOKUP cannot find E2. The example assumes Microsoft 365 or Excel 2021 or later, which support XLOOKUP. In those versions, XLOOKUP also has an optional not-found argument; with the same ranges, =XLOOKUP(E2,A2:A100,B2:B100,0) directly specifies zero for a missing match.

Use IFERROR instead when every error from the expression should receive the same fallback. Use IFNA when only a missing-match result should be replaced. If the formula is a lookup and the desired behavior is simply a custom not-found result, XLOOKUP’s own not-found argument can be clearer than wrapping the entire lookup in IFERROR.

Common Problems and Misconceptions

The formula still shows an error: Check that IFERROR wraps the entire expression that is failing, and that the parentheses and arguments are correctly placed.
The cell shows zero for a real problem: IFERROR may be hiding a broken reference, misspelled function, or invalid input. Inspect the original calculation before deciding the error should be suppressed.
The result is blank, not zero: The expression may be returning an empty string or another valid value rather than an error.
A total looks unexpectedly low: Replacing errors with zero can change summaries. Confirm whether excluded or undefined values should count as zero in that total.

A useful misconception to correct is that IFERROR fixes a formula. It does not. The underlying calculation can still be wrong; IFERROR only changes what the cell displays when evaluation ends in an error. For troubleshooting, temporarily remove the wrapper or inspect the calculation on its own. Once the cause is understood, either fix it or deliberately choose the fallback.

Using IFERROR with Arrays and Copied Formulas

In Microsoft 365, IFERROR can also work with dynamic-array results. When an expression returns an array of values, errors in that result can be replaced by the specified fallback in the corresponding output. This can be useful for a spilled calculation, but replacing every error in an array with zero may make it harder to identify which rows failed.

When you copy a formula down a worksheet, test both an ordinary row and a row that is supposed to trigger the fallback. Check that the references move as intended and that no valid zero is being mistaken for an error. If a formula is part of a larger report, verify downstream totals and charts too: a displayed zero is a numeric value and will generally be included in further arithmetic.

Frequently Asked Questions

What formula makes an Excel error show as zero?
Wrap the calculation in IFERROR and use 0 as the second argument. For example, with a calculation in C2, use =IFERROR(C2,0) in the result cell. The assumed layout is that C2 contains the expression or value to check.

Does IFERROR return zero for a blank cell?
Not simply because the cell looks blank. A blank or an empty string is not itself an error, so IFERROR does not automatically replace it with zero. The outcome depends on how the blank is used in the expression.

Can IFERROR hide #N/A and #DIV/0!?
Yes. Both are errors, so IFERROR can replace them with the chosen fallback. If you only want to replace #N/A and leave other errors visible, use IFNA instead.

Should I use IFERROR or IFNA for a lookup?
Use IFNA when a missing match is the only error you want to handle. Use IFERROR when every error from the lookup expression should be replaced. With XLOOKUP, you can also provide a not-found result directly in its optional fourth argument.

Can I return a blank instead of zero?
Yes. Use an empty string as the fallback, written as two double quotes, if you want errors to display as blank. Remember that this hides the error just as zero does, and the cell still contains a formula.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.