IFERROR in Excel: Leave Blank When a Formula Errors

To make an Excel formula show a blank instead of an error, wrap it in IFERROR and use an empty string as the fallback. With a calculation in A2, for example, enter =IFERROR(A2/0,""); Excel displays an empty-looking cell if the calculation produces an error.

The Basic IFERROR Formula

IFERROR checks the result of an expression. If the expression evaluates normally, Excel returns its result. If it returns an error, Excel returns the alternative value you specify. To leave the cell visually blank, that alternative is two double quotation marks with nothing between them: "". This is called an empty text string.

With a calculation in A2 that may fail, the general pattern is to put the calculation first and the blank text string second. For example, with a number in A2 and a divisor in B2, use =IFERROR(A2/B2,""). If A2 is 12 and B2 is 3, the result is 4. If B2 is 0, the division error is replaced by a blank-looking result.

IFERROR is available in modern Excel, including Microsoft 365 for Windows. It was introduced in Excel 2007, so it is also supported by later desktop editions. The function works in Excel for Mac and Excel for the web as well; the basic formula is the same.

How IFERROR Works

IFERROR has two arguments: the value or calculation to check, and what to return if that value is an error. It catches errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. It does not treat an ordinary zero, a valid text result, or an empty cell as an error.

That broad coverage is useful when a formula might fail for several different reasons. It is also a reason to be cautious: IFERROR does not tell you which error occurred or why. If a formula has a misspelled function name or an invalid cell reference, IFERROR can hide that mistake just as readily as an expected missing lookup.

The formula inside IFERROR is still evaluated. IFERROR changes the returned result; it does not repair the underlying data or change the calculation. If the input is corrected later, the formula recalculates and can show its normal result.

Practical Examples

Use the fallback that fits the calculation. These examples use commas between arguments, as in a standard English-language Excel installation. Each example notes its assumed cell layout.

Prevent a divide-by-zero message: With the value in A2 and the divisor in B2, enter =IFERROR(A2/B2,""). If A2 is 18 and B2 is 0 or blank, the cell appears blank; if B2 is 6, it returns 3.
Hide a missing lookup result: With lookup keys in A2:A6, return values in B2:B6, and the search key in E2, enter =IFERROR(VLOOKUP(E2,$A$2:$B$6,2,FALSE),""). If E2 matches a key, Excel returns its corresponding value from column B. If there is no match, the #N/A result is replaced by a blank-looking result.
Show a helpful message instead: With a numerator in A2 and divisor in B2, enter =IFERROR(A2/B2,"Check divisor"). For a zero divisor, the result is the text Check divisor rather than an error or an empty-looking cell.

In a lookup formula, hiding an error can keep a report tidy while source data is incomplete. But it can also make a misspelled key look like an intentionally empty result. Consider whether a reader needs to distinguish “not found” from “not applicable” before choosing a blank fallback.

A Blank-Looking Result Is Not an Empty Cell

The empty string "" looks like an empty cell, but a formula returning it is still present in the cell. This distinction matters when you use other formulas or tools to inspect the worksheet. For instance, ISBLANK checks whether a cell is truly empty; it returns FALSE for a cell containing a formula, even if that formula displays nothing.

With a formula in C2 that may return an empty string and you want to test whether C2 is genuinely empty, enter =ISBLANK(C2). The result is FALSE when C2 contains an IFERROR formula, even if that formula currently displays a blank. Functions that count or evaluate cells may also treat formulas returning empty text differently from truly unused cells, so verify the behavior that matters for your workbook.

This difference is usually harmless when the goal is presentation: the worksheet looks cleaner, and the formula remains ready to calculate when inputs change. It matters more when exporting data, checking for missing entries, or applying logic that depends on whether a cell is actually empty. If downstream steps require true empty cells, a formula alone cannot turn its own cell into a genuinely empty cell.

IFERROR Compared with IFNA

IFERROR catches all Excel error values. IFNA catches only #N/A. That makes IFNA a narrower choice for formulas where a missing lookup match is expected but other errors should remain visible.

With a lookup key in E2, keys in A2:A6, and return values in B2:B6, enter =IFNA(VLOOKUP(E2,$A$2:$B$6,2,FALSE),"") to hide a missing-match error while allowing other errors to show. For example, an unavailable key returns a blank-looking result, but a different error is not automatically concealed.

Choose IFERROR when any error in the expression should produce the same fallback. Choose IFNA when the specific condition you want to handle is “no matching value.” Neither function fixes a bad reference or incorrect lookup setup; a narrowly targeted fallback can make those problems easier to notice.

Common Problems and How to Avoid Them

The formula shows 0 instead of blank: Check that the second IFERROR argument is "", not 0. Also confirm the calculation is actually producing an error; IFERROR leaves a valid zero unchanged.
The cell looks blank but is not empty: That is expected when a formula returns "". The formula remains in the cell, so selecting it will show the formula bar contents.
A real mistake is hidden: Temporarily remove IFERROR or replace the blank fallback with a visible message while troubleshooting. Review references, input values, and function names before restoring a quiet display.
The formula is not accepted: Some regional settings use semicolons rather than commas as argument separators. If Excel flags the commas, follow the separator used by other formulas in that installation; the function name and logic stay the same.
Blank outputs affect a chart or summary: Different chart types and functions can treat empty text, true blanks, and zeros differently. Check the specific chart or calculation rather than assuming a blank-looking cell behaves exactly like an unused cell.

For Windows, enter or edit a formula in the formula bar or directly in the selected cell, then press Enter. On Mac, the same formula entry process applies; keyboard shortcuts can differ by keyboard and Excel version, but no special command is needed to use IFERROR. Excel for the web also accepts the function and uses the same formula logic.

When to Use a Different Approach

Use IFERROR when the error is an expected possibility and the chosen fallback communicates the right meaning. A blank can be appropriate for a calculation that is not ready until someone enters a divisor, or for a report where unmatched optional records should be omitted visually. If the error signals bad data, a broken reference, or a required missing value, displaying the error or a clear message is often more useful.

You can also prevent a known condition rather than catching every possible error. For division, an IF test can check whether the divisor is zero before dividing. With the numerator in A2 and divisor in B2, use =IF(B2=0,"",A2/B2). When B2 is zero, it returns an empty string; otherwise, it calculates the quotient. This approach makes the intended condition explicit, though it will not catch unrelated errors in the calculation.

For a lookup where only the missing-match case should be hidden, IFNA is often more precise. For a report intended for review, returning text such as “Missing” or “Check input” may be safer than a blank because it makes incomplete information visible. The right choice depends on what a blank means to the people who will use the sheet.

Frequently Asked Questions

How do I make IFERROR return a blank in Excel?
Put "" as IFERROR’s second argument. With a calculation in A2, for example, =IFERROR(A2/0,"") displays a blank-looking result when the division error occurs.

Does IFERROR hide every Excel error?
Yes. It substitutes the fallback for any error value produced by its first argument, not just divide-by-zero or missing lookup errors. Use IFNA instead when you only want to handle #N/A.

Can I make an IFERROR result truly empty?
No. A formula can return empty text with "", but the cell still contains the formula. A truly empty cell has no formula or value in it.

Why does my IFERROR formula still show zero?
Zero is a valid calculation result, not an error, so IFERROR returns it unchanged. If zero should also appear blank, add a separate condition that checks for zero, making sure that hiding zero is appropriate for your data.

Should I use IFERROR or IFNA for VLOOKUP?
Use IFNA if the only expected problem is that the search key has no match. Use IFERROR if every possible error from the lookup expression should be replaced, while remembering that this can conceal unrelated formula problems.

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.