Yes. In Excel for Microsoft 365, use IFERROR with an empty text string as its second argument to display a blank when a formula returns an error. If the calculation is in A2, enter =IFERROR(A2,""); the cell will look empty when A2 contains an error, but it will still show A2’s result when there is no error.
The basic IFERROR formula
IFERROR checks an expression and returns one result if that expression works, or another result if it produces an error. Its syntax is IFERROR(value, value_if_error). To show nothing for an error, set value_if_error to "", which is an empty text string made from two quotation marks.
For example, suppose A2 contains a calculation that may fail. The formula =IFERROR(A2,"") displays the value of A2 when it is valid and displays no visible text when A2 evaluates to an error. Usually, however, you put the calculation itself inside IFERROR so the formula handles the error directly. With a numerator in A2 and a denominator in B2, use =IFERROR(A2/B2,""). If A2 is 125 and B2 is 5, the result is 25; if B2 is 0, Excel displays a blank instead of #DIV/0!.
To enter it in Excel for Microsoft 365 on Windows, select the cell where you want the result, type the formula beginning with an equals sign, and press Enter. Fill or copy the formula down to apply it to other rows; relative references such as A2 and B2 adjust automatically. The same formula approach works in Excel for Mac and Excel for the web, although ribbon layout and some keyboard shortcuts differ between platforms.
Which errors IFERROR hides
IFERROR does not target just one particular error. It returns the fallback result for any error produced by its first argument. Common examples include #DIV/0! for division by zero, #N/A when a lookup cannot find a match, #VALUE! when a formula receives an unsuitable value, #REF! for an invalid reference, and #NAME? when Excel does not recognize text in a formula.
This broad coverage makes the function convenient, but it also means an empty result can conceal several different causes. If a lookup suddenly stops finding records, IFERROR may make the output appear tidy while hiding the fact that a key is misspelled. If a reference was accidentally deleted, replacing the visible #REF! with a blank does not repair the formula. Treat a blank as a presentation choice, not as proof that the underlying calculation is correct.
IFERROR handles errors arising while Excel evaluates its first argument. It does not prevent an error from occurring or correct the data that caused it. The original inputs and formula logic remain important, especially in workbooks used for reporting, reconciliation, or decisions.
Practical examples for formulas and lookups
A common use is suppressing division errors in a table of rates, ratios, or unit costs. With revenue in A2 and units in B2, use =IFERROR(A2/B2,""). If revenue is 480 and units are 12, the result is 40. If units are zero or the denominator contains text that makes the division invalid, the formula displays a blank. If zero units should instead be reported as a meaningful zero, use a different rule rather than hiding that case.
IFERROR can also clean up a lookup table. Suppose product codes are in A2:A10, product names are in B2:B10, and the code to find is in E2. Enter =IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"") in the result cell. When E2 matches a code in column A, the formula returns the corresponding name from column B; when no exact match is found, VLOOKUP returns #N/A and IFERROR displays a blank. The final argument, FALSE, requests an exact match.
For a summary that should remain empty until a calculation is possible, the same technique can wrap a longer expression. With values in A2 and B2, =IFERROR((A2-B2)/B2,"") displays the relative change when B2 is a usable nonzero denominator and otherwise shows a blank. As formulas become more complex, consider whether every possible error should receive the same treatment. A single empty fallback can make distinct issues look identical.
A displayed blank is not a truly empty cell
The result "" is empty text, not the same thing as clearing a cell. The cell still contains a formula. That distinction matters when other formulas, filters, or checks inspect it. For instance, ISBLANK returns FALSE for a cell containing an IFERROR formula, even when that formula currently displays nothing. Excel’s COUNTBLANK generally treats a formula result of empty text as blank, while COUNTA counts a cell containing a formula, including one that returns empty text.
That difference can affect totals and data-cleaning steps. If a downstream formula needs to tell the difference between a genuinely unused cell and a formula that is suppressing an error, checking whether the cell is blank may not be enough. You may need to test the source inputs or use a separate status column that identifies why a result is unavailable.
Formatting does not change this behavior. Applying a custom number format that hides a value may make a cell look blank, but it does not remove the value or the formula either. IFERROR is useful when you specifically want a formula to return empty text on errors; it is not a way to erase the cell’s contents.
When to show a blank—and when not to
A blank is often appropriate in a presentation table where an unavailable result would otherwise clutter a report, and where readers understand that missing output means “no usable result.” It can also help when a formula is copied down in advance and later rows do not yet contain complete inputs. In those situations, keeping the display clean can make the populated results easier to scan.
It is less suitable when the error signals a problem someone must fix. In an audit worksheet, a data import check, or a calculation used to support a decision, a visible error can be a useful warning. Hiding it may cause a reviewer to interpret the empty cell as intentionally unused. Consider displaying a clear label such as “Check input” instead of a blank when users need to know that something went wrong.
- Use a blank when an unavailable result is expected and the reason is clear from the surrounding data.
- Keep an error visible when it points to a broken reference, unexpected input, or a formula that needs investigation.
- Use a descriptive message when people need to distinguish “not applicable” from “calculation failed.”
- Document the meaning of blank outputs if others will rely on the workbook.
Common problems and how to avoid them
One frequent mistake is using two quotation marks incorrectly. The fallback must be an empty text string: "". A space between the quotation marks is not empty; it returns a space that may look blank but can affect comparisons, trimming, and exports. A single quotation mark is not the correct Excel text delimiter.
Another issue is wrapping too much of a formula in IFERROR. Because it catches many error types, it can hide a typo in a function name, an invalid reference, or a data problem that should be corrected. During formula development, leaving errors visible can help you diagnose the cause. Add error handling once you know which failures are expected and what users should see in those cases.
Also check whether the error is actually generated by the expression you wrapped. If a cell is blank because a formula deliberately returns empty text, that is not an error, so IFERROR does not change it. Likewise, IFERROR will not turn a normal zero into a blank: zero is a valid numeric result. If zero needs a different display, use a separate condition or number formatting based on the intended meaning.
Finally, a blank result can affect charts and exported data differently from a truly empty cell, depending on the chart, export method, and how the receiving application interprets formulas. If a blank-looking output feeds another report, test that full workflow rather than assuming it will always be treated as missing data.
IFERROR compared with IF and IFNA
Use IFERROR when you want the same fallback for any error. Use IFNA when only a not-found result such as #N/A should be handled and other errors should remain visible. That narrower choice can be useful for lookups: a missing item may be expected, while a broken reference or invalid argument should still alert you.
Use IF when you can identify the condition before performing the calculation. For example, if the only concern is a denominator of zero, you can test that condition directly. With a numerator in A2 and denominator in B2, use =IF(B2=0,"",A2/B2). If A2 is 125 and B2 is 0, the result is blank; if B2 is 5, the result is 25. This approach makes the intended rule explicit, but it will not handle every other possible error in the division expression.
Choose based on what you want the workbook to communicate. IFERROR is concise and broad, IFNA is more selective for missing matches, and IF is useful for a known condition such as zero or an empty input. In a well-designed workbook, the fallback should express a deliberate rule rather than simply make every visible error disappear.
Frequently Asked Questions
Does IFERROR make a cell truly empty?
No. It returns empty text, so the cell looks blank but still contains a formula. Functions that inspect cell contents can distinguish it from a cell that has been cleared.
How do I show blank instead of #N/A?
Wrap the lookup or other expression in IFERROR and use "" as the fallback. If you want to suppress only #N/A and keep other errors visible, IFNA is the more targeted alternative.
Can IFERROR hide a zero?
No. Zero is a valid result, not an error. IFERROR leaves it visible. Use an explicit condition or suitable number formatting if the intended display for zero is different.
Why does my blank-looking cell count as filled?
The cell contains a formula even if that formula returns empty text. Some counting and blank-checking functions treat formula cells differently, so choose a test that matches whether you mean “no formula” or “no displayed result.”
Should I use IFERROR for every formula?
Not automatically. It can make reports easier to read, but it can also conceal mistakes and unexpected data. Use it when the handled errors are understood and the chosen fallback gives users the right interpretation.
Related
- CAGR Return in Excel: Calculate Investment Growth
- How to Calculate CAGR in Excel for 3 Years
- How to Calculate Row Percentage in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Not Working Between Workbooks? How to Fix ItWhen VLOOKUP fails between Excel workbooks, check the external reference, use an exact match, and confirm that the…
- VLOOKUP Formula Correct but Not Working? Common FixesIf your VLOOKUP formula looks correct but is not working, first make it an exact match by setting…
- Excel Filter Not Working in SharePoint? Causes and FixesIf a filter is not working in Excel in SharePoint, first check that the filter covers the full…
- Excel #NULL! Error Fix: Find and Correct Range ReferencesTo fix Excel’s #NULL! error, check for a space between cell ranges: Excel treats that space as an…
- Excel Table Showing Formula Not Result? Fix ItIf an Excel table is showing a formula instead of its result, first turn off Formulas > Formula…
- VLOOKUP #REF! Error: Why It Happens and How to Fix ItA VLOOKUP #REF! error means the formula contains an invalid cell or range reference. The most common cause…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.