Show Errors as Blank in Excel: Formula and Options

Formulas & functions · Updated November 20, 2026

Yes. In Excel for Microsoft 365, wrap a formula in IFERROR and return an empty text string when it fails. For example, if a formula divides A2 by B2, use =IFERROR(A2/B2,""); an error such as division by zero will then appear as a blank cell.

How IFERROR makes an error look blank

IFERROR checks the result of an expression. If the expression produces a value, Excel shows that value; if it produces an error, Excel returns the alternative value you specify. In this case, "" is an empty text string, which looks blank in the worksheet. It does not remove the underlying formula or repair the reason the error occurred.

Assuming A2 contains 12 and B2 contains 0, the unprotected division returns #DIV/0!. With the following formula in C2, the displayed result is blank instead:

With the numerator in A2 and denominator in B2, enter this in C2:

=IFERROR(A2/B2,"")

For a non-error result, the formula still displays the calculation. If A2 contains 12 and B2 contains 3, C2 displays 4. If B2 is zero, it displays nothing. This makes the method useful in reports where visible error codes would distract from the results, but it can also conceal a mistake if used without checking the underlying data.

Apply the formula to a worksheet

To change an existing calculation, put the original formula inside IFERROR rather than replacing it with a different calculation. For example, if D2 currently contains a formula that calculates a margin, edit it so that the complete calculation is the first argument and "" is the second. Keep the original parentheses and operators intact.

  1. Select the cell containing the formula, such as D2, and note or copy its existing formula.
  2. Wrap that formula in IFERROR(…,""). Replace the ellipsis with the original expression; the ellipsis is explanatory and is not part of a working Excel formula.
  3. Press Enter and check both an ordinary result and a row that would previously have shown an error.
  4. Copy the updated formula down the column using the fill handle, or copy and paste it into the other formula cells. Excel adjusts relative references as it fills the formula.

For instance, suppose column A contains revenue and column B contains cost. If A2 is 100 and B2 is 70, the difference divided by revenue is 0.3, or 30%. To leave the result blank when the calculation fails, enter this in C2:

With revenue in A2 and cost in B2, enter this in C2:

=IFERROR((A2-B2)/A2,"")

For the stated values, C2 returns 0.3. If A2 is zero, the division would normally produce #DIV/0!, so this formula displays blank instead. The formula does not determine whether zero revenue is a legitimate case; it only controls what appears when Excel detects an error.

Choose between IFERROR and IFNA

Use IFERROR when the goal is to hide any error generated by the enclosed expression. It handles errors such as #DIV/0!, #VALUE!, #REF!, and #N/A. That broad coverage is convenient, but broad handling can also hide an unintended broken reference or incompatible data.

If the only expected problem is a lookup value that is not found, IFNA is often more precise. It substitutes the alternative only for #N/A, leaving other errors visible so they can be investigated. For example, suppose E2 contains a product code, column A contains product codes, and column B contains product names. The following returns a blank when the code is not found, while allowing other error types to remain visible:

With the lookup code in E2 and product codes and names in A2:B10, enter this in F2:

=IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"")

If E2 is absent from A2:A10, F2 is blank. If a problem other than “not found” occurs, Excel can still display the corresponding error. This distinction helps reveal problems such as an invalid reference rather than making every unexpected result appear empty. Both functions are available in current Excel versions, including Microsoft 365.

Handle blank inputs as well as errors

A blank-looking result is not necessarily an error. Sometimes a formula returns an unwanted zero or calculates from incomplete input. In that situation, decide what should happen when a required input is missing, then test for it explicitly. That makes the rule easier to understand than relying on error handling alone.

For example, suppose A2 is a quantity and B2 is a unit price. You want no result until both inputs are present, and you also want to suppress any calculation error. Put this formula in C2:

With quantity in A2 and unit price in B2, enter this in C2:

=IFERROR(IF(OR(A2="",B2=""),"",A2*B2),"")

If either input is empty, C2 displays blank. If A2 is 4 and B2 is 2.5, it returns 10. The outer IFERROR handles errors; the inner IF and OR implement the separate rule that incomplete rows should remain blank. This is useful for forms and lists where empty rows are expected, rather than errors to diagnose.

Be careful not to treat a legitimate zero as missing. A test such as A2="" checks for an empty cell or empty text, whereas a test such as A2=0 treats the number zero as a special case. Which test is right depends on the meaning of the data.

A formula blank is not a truly empty cell

Returning "" makes a cell look empty, but the formula remains in it. That difference matters when other formulas, filters, charts, or data-cleaning steps use the result. For example, ISBLANK returns FALSE for a cell that contains a formula, even when that formula displays an empty string. A cell with no content at all, by contrast, is truly blank.

Some counting functions treat formula-generated empty strings differently from other functions. COUNTBLANK treats a formula result of "" as blank, while COUNTA counts a cell containing a formula as nonempty. So a column can look empty in some rows while still contributing to a nonempty-cell count. If a later calculation depends on whether a cell is genuinely unused, check the function’s treatment of formulas rather than relying on appearance.

Similarly, a formula-generated blank is not the same as a numeric zero. It may affect chart handling or downstream calculations differently. If a chart should omit these results, check the chart’s handling of empty cells and test it with the workbook’s actual data. Do not assume that every feature interprets an empty string exactly like a cell that has been cleared.

Common problems and how to diagnose them

  • The error still appears: Check that the error-producing expression is inside the first argument of IFERROR, and that the formula was entered in the cell whose result you are viewing. If a different formula elsewhere in the workbook produces the error, wrapping one cell’s formula will not affect it.
  • A blank appears when a value should be present: The formula may be suppressing an error caused by text in a number field, a missing lookup match, or an invalid reference. Temporarily inspect the original expression without the wrapper, or replace the blank alternative with a message such as "Check input" while troubleshooting.
  • The formula displays as text: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell to General, re-enter the formula, and confirm that Excel calculates it rather than displaying the characters.
  • Only some rows behave differently: Compare the formulas and their references across those rows. A copied formula may point to a different range or include a blank or text value. Check whether references should be fixed with dollar signs before filling the formula down.
  • You still see a green error indicator: The indicator is a separate error-checking notice; it is not the same as the displayed error value. Review the formula and data instead of assuming that hiding the visible error corrected the cause.

To inspect a cell’s formula, select it and look in the formula bar. In Excel for Microsoft 365 on Windows, the Formulas tab also includes Formulas > Error Checking for navigating formula errors. On Mac, the Formulas tab provides Error Checking as well, though the ribbon layout can vary slightly by version. These tools help investigate the cause; they do not automatically make a formula-generated blank into a truly empty cell.

Alternatives to hiding errors

Blanking every error is not always the clearest choice. Consider whether the workbook should instead show a useful message, correct the input, or make the error easy to find. For a shared operational sheet, a message such as “Check quantity” can be more informative than a blank; for a polished summary, blanks may be appropriate while a separate validation check flags incomplete records.

  • Fix the cause: Correct a zero denominator, missing lookup key, text value, or broken reference when that input is invalid. This preserves the true result rather than concealing the problem.
  • Handle only the expected error: Use IFNA for a lookup that may legitimately have no match, and leave unrelated errors visible.
  • Show a message: Return explanatory text when a person needs to take action. Remember that text results may not be suitable for later numeric calculations.
  • Use a separate status column: Keep the calculation available for review and label rows that need attention. This can be safer than making a report look complete when data is missing.

Excel’s error-checking settings can control some warning indicators, but changing those indicators does not change the underlying formula result. Likewise, formatting a cell to hide text is not a reliable substitute for handling the formula: it can hide useful values as well as errors. For most formula-driven worksheets, explicit logic with IFERROR or IFNA is easier for another person to inspect.

Frequently Asked Questions

Can I make all Excel errors appear blank?
Yes. Wrap each relevant formula in IFERROR and use "" as its alternative result. Excel applies the change only to formulas you edit; it does not provide a single worksheet setting that safely converts every error into a blank while preserving each formula’s intent.

Does IFERROR delete or fix the error?
No. It replaces the displayed result of an error with the value you specify. The original calculation and its cause remain, so use the formula bar or a temporary diagnostic message to investigate problems that should not be hidden.

Why does a blank cell with a formula fail ISBLANK?
Because the cell contains a formula, even if its displayed result is an empty string. ISBLANK tests whether the cell has content, not whether its visible display looks empty. Choose a check that matches whether you need to detect a missing value or a formula-generated blank.

Should I use IFERROR or IFNA for a missing lookup?
Use IFNA when “not found” is the only condition you intend to suppress. Use IFERROR if all errors from that expression should be hidden. The narrower choice generally makes unexpected problems easier to notice.

Can I show a dash instead of a blank?
Yes. Use a text value such as "-" as the alternative result instead of "". The cell will display a dash, but that result is text, not a numeric zero, so consider how other formulas and charts will use it.

Does this work in Excel for Mac or Excel for the web?
Yes. IFERROR and IFNA work in current Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. Ribbon placement can differ, but the formula syntax and basic behavior are the same.

Related

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.