Excel IFERROR: Return a Blank Instead of an Error Value

To show a blank when an Excel formula returns an error, wrap it in IFERROR and use an empty text string as the fallback. For example, with a numerator in A2 and a denominator in B2, enter =IFERROR(A2/B2,"") in C2; if B2 is zero, C2 displays nothing instead of #DIV/0!.

What IFERROR Does

IFERROR(value, value_if_error) evaluates its first argument and returns that result if it is not an error. If the calculation does produce an error, Excel returns the second argument instead. The empty string "" is text with zero characters, so the cell looks blank even though it contains a formula.

In Microsoft Excel for Microsoft 365 on Windows, the function can catch errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. It can also catch errors produced by newer Excel features. IFERROR does not fix the underlying cause; it replaces the visible error with the fallback you choose.

Excel for Mac and Excel for the web use the same IFERROR syntax. The formula’s argument separator can depend on your regional settings: many installations use commas, while some use semicolons. If Excel rejects a formula with commas, check the separator your installation expects.

Return a Blank for a Division Error

For the basic division example, suppose A2 contains the amount to divide and B2 contains the divisor. Put the formula in C2:

=IFERROR(A2/B2,"")

If A2 is 12 and B2 is 3, C2 displays 4. If B2 is 0, the division produces #DIV/0!, so the cell displays blank. If the formula is in C2, copy the fill handle down to apply it to other rows; Excel adjusts the relative references for each row.

This can be useful in a report where an error would distract from the results. But consider why the error occurs. A zero divisor might mean that a record is not ready, or it might reveal incorrect input. Hiding every error can make those situations harder to notice, so use the fallback deliberately.

Use IFERROR with Lookups and Other Formulas

IFERROR can wrap more than arithmetic. For example, assume the value to find is in A2, the lookup keys are in D2:D100, and the corresponding results are in E2:E100. In Microsoft 365, Excel 2021, or a later version with XLOOKUP, use:

=IFERROR(XLOOKUP(A2,D2:D100,E2:E100),"")

If XLOOKUP cannot find the key, it returns #N/A; IFERROR changes that result to a visually blank cell. XLOOKUP also has its own optional “if not found” argument, so you can instead enter:

=XLOOKUP(A2,D2:D100,E2:E100,"")

This second approach returns blank specifically when there is no match, without wrapping the entire lookup in a general error handler. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, a lookup formula such as VLOOKUP can also be wrapped in IFERROR, provided its arguments match the layout of your data.

IFERROR works around any error in the expression it wraps, not just the error you expect. If a lookup range reference is broken, for example, the blank fallback can hide that problem too. Decide whether a blank is the right message for every possible failure before using it.

Choose Between IFERROR, IFNA, and a Direct Check

Use IFERROR when you want the same fallback for any error in a calculation. If you only want to hide a missing lookup result, IFNA is narrower: it responds to #N/A and lets other errors remain visible. With the same lookup layout—search value in A2, keys in D2:D100, and results in E2:E100—this formula returns blank for a missing match but does not hide a different error:

=IFNA(XLOOKUP(A2,D2:D100,E2:E100),"")

Another option is to test for a known condition before calculating. With the numerator in A2 and denominator in B2, this formula returns blank when the denominator is zero:

=IF(B2=0,"",A2/B2)

The direct check makes the intended rule clear: zero means “show nothing.” Unlike IFERROR, it will not suppress unrelated errors in the division. However, a blank cell in B2 is treated as zero in this comparison, and text or other unexpected values can still cause problems in the calculation. Select the method that matches the data rules, not simply the method that produces the quietest worksheet.

A Formula That Looks Blank Is Not Truly Empty

When IFERROR returns "", the formula remains in the cell. The result looks empty, but the cell is not the same as a cell that has never contained a value or formula. This distinction matters if another formula or feature checks whether a cell is empty.

ISBLANK returns FALSE for a cell containing a formula, even when that formula displays an empty string.
COUNTA counts a formula cell as occupied, including one whose displayed result is "".
COUNTBLANK generally treats a formula result of "" as blank.

For instance, if C2 contains the IFERROR formula, a check based on ISBLANK(C2) will not treat C2 as genuinely empty. If later calculations depend on the displayed result, test for an empty string where appropriate, or test the original inputs instead. An empty string is also different from a space: " " contains a character and can affect comparisons, trimming, and display.

Common Problems and Ways to Diagnose Them

If the error still appears, check that IFERROR surrounds the complete expression that produces it and that the parentheses are balanced. If the formula is rejected as invalid, confirm that the function name is spelled correctly, the quotes around the empty string are straight double quotes, and your Excel settings use the separators in the formula.

If the cell appears blank but downstream results are wrong, remember that the blank is a text result, not a numeric zero. A formula that expects a number may treat it differently from zero. Do not replace the empty string with 0 unless zero is a truthful value for the situation; a zero can affect averages, totals, charts, and decisions.

Also check whether the underlying error should be corrected instead. A #REF! error can indicate that a formula refers to a deleted cell, and #VALUE! can point to incompatible data types. A blank display can conceal these issues. During formula development, temporarily remove IFERROR or use a visible fallback such as "Check input" so that problems are easier to identify.

Apply the Formula Safely in a Worksheet

A practical workflow is to build and check the calculation first, then decide which errors deserve a blank. For a recurring column of calculations, use these steps:

Enter the unwrapped calculation in a test row and confirm the normal result.
Identify the likely errors and decide whether each should be corrected, shown, or replaced with a blank.
Wrap the calculation in IFERROR only if the same fallback is appropriate for all errors it can produce.
Test one normal row and at least one row that would otherwise produce an error.
Fill the formula down and check any totals, counts, filters, or charts that use the results.

For a temporary debugging view, replace the empty-string fallback with explanatory text, or inspect the original calculation in a separate cell. Once you have verified the source data and formula, you can restore the blank fallback if it suits the finished report.

Frequently Asked Questions

How do I show a blank instead of #N/A in Excel?
Wrap the formula that may return #N/A in IFERROR with "" as its second argument. If the only error you want to hide is #N/A, IFNA is more specific.

Does IFERROR change the original error?
No. IFERROR changes what the formula displays by returning a fallback value. It does not repair the source data or correct the calculation that caused the error.

Can IFERROR return a message instead of a blank?
Yes. Use a text fallback such as "Check value" instead of "". A message can be more useful while a workbook is being checked, while a blank may suit a finished report.

Why does ISBLANK say FALSE when the cell looks empty?
The cell still contains a formula. When that formula returns "", Excel displays no characters, but ISBLANK does not regard the formula cell as truly empty.

Will IFERROR hide every problem in a formula?
It replaces any error returned by the expression it wraps, but it does not detect every possible data-quality problem. A formula can return a plausible but incorrect value without producing an Excel error, so blanking errors is not a substitute for checking inputs and logic.

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.