To make an Excel formula display a blank instead of an error, wrap the calculation in IFERROR and use an empty text string as its fallback. For example, with a numerator in A2 and a denominator in B2, enter =IFERROR(A2/B2,""); if division produces an error, the cell looks blank, while a valid calculation still displays its result.
How IFERROR works
IFERROR evaluates an expression and returns that expression’s result if it succeeds. If the expression returns an Excel error, the function returns the alternative value you specify instead. The empty text string represented by two quotation marks tells Excel to display nothing in the cell. The formula remains in the cell; it has not been deleted.
The function can handle errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. That broad coverage is convenient when a formula may fail for several reasons, but it also means a problem you might want to notice can be hidden. IFERROR does not repair the underlying data or calculation. It changes what the formula returns when an error occurs.
In Microsoft 365 for Windows, type the formula into the cell or the formula bar and press Enter. You can fill it down to apply it to other rows. The function and formula syntax are the same in Excel for Mac and Excel for the web; the main differences are in the interface and available shortcuts, not in this basic use of IFERROR.
Basic formula and worked example
Suppose column A contains amounts and column B contains quantities. You want the amount per item, but some rows have no quantity yet. With the amount in A2 and quantity in B2, use =IFERROR(A2/B2,""). If A2 is 100 and B2 is 0, Excel displays a blank instead of #DIV/0!. If A2 is 100 and B2 is 4, the result is 25.
To reuse the formula, select the cell and drag its fill handle down, or copy and paste it into the rows below. Excel adjusts relative references as the formula moves: the next row will refer to A3 and B3. Check that this is what you intend, especially if your data includes totals or other rows that should not be calculated in the same way.
You can put any formula or expression in IFERROR’s first argument, not just division. With a lookup value in E2 and a two-column lookup table in A2:B100, where the first column contains keys and the second contains results, use =IFERROR(VLOOKUP(E2,A2:B100,2,FALSE),""). If VLOOKUP cannot find the key, it returns #N/A, and IFERROR displays a blank. If the key is found, the matching value is returned.
Choose between IFERROR, IFNA, and an IF test
IFERROR is suitable when you want to hide any error produced by the expression. For a lookup, however, you may only want to hide the normal “not found” result while leaving other errors visible for investigation. In that case, IFNA is more specific: it replaces #N/A but does not suppress errors such as #REF! or #VALUE!.
With a lookup value in E2 and a key-and-result table in A2:B100, use =IFNA(VLOOKUP(E2,A2:B100,2,FALSE),"") when only a missing match should look blank. If the table reference is broken, the resulting reference error remains visible, which can help you find the cause rather than silently concealing it.
Another option is to test for a known condition before doing the calculation. With the numerator in A2 and denominator in B2, =IF(B2=0,"",A2/B2) displays a blank when the denominator is zero and otherwise divides. This makes the rule explicit, but it will not catch unrelated errors: if A2 contains text that cannot be used in the calculation, that error can still appear.
Use a condition-based IF when you know exactly what should trigger a blank and want other unexpected errors to remain visible. Use IFNA for missing-match errors, and use IFERROR when any error in the wrapped expression should be replaced. Choosing the narrowest appropriate method makes a workbook easier to check.
A blank-looking cell is not truly empty
An IFERROR formula returning an empty string looks empty on the worksheet, but the cell still contains a formula and its result is text of zero length. That distinction matters if another formula or feature checks whether a cell is empty. For example, ISBLANK returns FALSE for a cell containing a formula, even when that formula displays nothing.
With a formula in C2 that returns an empty string when appropriate, =ISBLANK(C2) returns FALSE because C2 contains a formula. A check such as =C2="" can treat the displayed empty string as equal to an empty string. These checks answer different questions: ISBLANK tests whether the cell has anything in it, while the comparison checks the cell’s value.
The distinction can also affect counts, filters, and charts. COUNTBLANK generally counts cells that are empty or return an empty string, while COUNTA can count a cell containing a formula even if its displayed result is blank. Filters may show blank-looking formula results in a blank category, depending on the data and feature. If a downstream process requires genuinely empty cells, a formula cannot make its own cell truly empty; you may need to change the data workflow or remove formulas from those cells.
Prevent the error when you can
Hiding an error is not always the best fix. If an error signals missing input, incorrect data, or a damaged reference, showing it during development can reveal what needs attention. A blank is most useful when the error is an expected part of the workflow, such as a lookup that has not found a match yet or a ratio that is not meaningful until a denominator is entered.
For a denominator that should be blank or zero until data arrives, a targeted IF test can express that rule directly. With the numerator in A2 and denominator in B2, =IF(OR(B2="",B2=0),"",A2/B2) returns a blank if B2 is empty or zero, and otherwise calculates the ratio. If the denominator contains an unexpected text value, the division may still show an error, which can be useful as a warning.
Before suppressing an error, consider these checks:
- Confirm that the source cells contain the expected values and data types.
- Check whether a zero denominator is expected, or whether it indicates missing or incorrect data.
- For a lookup, verify that the lookup value and table keys use compatible formats, such as numbers compared with numbers.
- Inspect references for deleted or moved cells if you see
#REF!. - During formula building, leave errors visible until you understand why they occur; add a fallback when the behavior is intentional.
Common problems and troubleshooting
If the cell still shows an error, make sure the error-producing formula is inside IFERROR’s first argument. A formula that refers to a different cell will not hide an error in that other cell unless the reference itself is included in the wrapped expression. Also check that the formula uses the correct parentheses and that the fallback is inside the function.
If Excel displays the formula text instead of calculating it, the cell may be formatted as Text, or Show Formulas may be turned on. Change the cell format to General, then re-enter the formula. In Excel for Microsoft 365 on Windows, the Show Formulas toggle is on the Formulas tab; Ctrl+` toggles it. On Mac, the shortcut is Control+` in many configurations, though keyboard layouts can differ; use the Formulas tab if the key combination is unavailable.
If a blank appears where you expected a result, the calculation may actually be producing an error that IFERROR is concealing. Temporarily remove the wrapper or replace the empty-string fallback with a message so you can diagnose it. With a numerator in A2 and denominator in B2, =IFERROR(A2/B2,"Check the inputs") displays that message when the division errors, making the problem easier to spot.
Regional settings can affect how Excel expects formulas to be entered. This article uses commas between function arguments, as specified for this workbook context. Some installations configured for other locales use semicolons instead. If Excel rejects a formula despite correct logic, check the list separator used by your installation.
Good practices for readable workbooks
A blank fallback keeps reports tidy, but it can make a worksheet look complete when data is missing. Consider whether users need to distinguish “not applicable,” “not entered yet,” and “calculation failed.” Those are different states, and using the same blank for all of them may make later review harder.
- Use an informative message such as “Not found” when a user needs to know why a result is missing.
- Prefer IFNA for lookups when only an unmatched key is an expected issue.
- Keep errors visible in calculation or audit sheets where diagnosing bad inputs matters.
- Document what a blank result means, particularly when other people will use the workbook.
- Test representative rows, including valid input, missing input, zero values, and unexpected text.
For a polished report, you can keep the underlying calculation in a working column and present a separate display column with a chosen fallback. That is not always necessary, but it can preserve diagnostic information while making a summary easier to read. Whatever approach you use, verify totals and downstream formulas because a blank-looking result may still be a formula result rather than a genuinely empty cell.
Frequently Asked Questions
How do I make #DIV/0! appear blank in Excel?
Wrap the division in IFERROR and provide an empty text string as the fallback. With a numerator in A2 and denominator in B2, enter =IFERROR(A2/B2,""). The result displays blank if the division returns an error.
Does IFERROR remove the error from the worksheet?
No. It returns a substitute value instead of showing the error from the wrapped expression. The original issue may still exist in the inputs or formula, so use a message or inspect the unwrapped calculation when troubleshooting.
Can I make a formula cell truly empty with IFERROR?
No. The cell still contains a formula. An empty-string result only makes it look blank; functions such as ISBLANK can still detect that the cell is not truly empty.
Should I use IFERROR or IFNA for a lookup?
Use IFNA if you only want to hide a missing-match #N/A result. Use IFERROR if you intentionally want to replace every error from the lookup expression, including errors that may indicate a different problem.
Why is Excel showing my formula instead of a blank or result?
Check whether the cell is formatted as Text or whether Show Formulas is enabled. Change the format to General and re-enter the formula, or turn off Show Formulas from the Formulas tab.
Related
- How to Split an Excel Row in Half
- How to Split an Excel Sheet in Half Without Losing Data
- How to Split an Excel File in Half: A Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Formula Is Correct but Not Working? How to Fix ItIf a formula is correct but not working in Excel, first check whether the cell is formatted as…
- Hyperlink Not Working in Microsoft Word? Causes and FixesIf a hyperlink in Microsoft Word will not open, first try holding Ctrl while clicking it on Windows,…
- Excel Runtime Error 9: Subscript Out of Range?Excel VBA runtime error 9, “Subscript out of range,” means code tried to access an item that does…
- Excel Data Validation Not Working? Fix Common CausesIf Excel data validation is not working, select the affected cells and check Data > Data Validation: confirm…
- Why Is My SUMPRODUCT Formula Not Working? Checks and FixesA SUMPRODUCT formula usually fails because its ranges do not have matching dimensions, its data contains errors or…
- Excel File Too Large Because of Blank Cells? Fix ItYes—an Excel workbook can become unusually large when cells that look blank contain formatting, spaces, formulas, or other…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.