To leave a cell blank when a formula returns an error in Excel, wrap the formula in IFERROR and use an empty text string as its fallback. If A2 contains a numerator and B2 a denominator, enter =IFERROR(A2/B2,""); Excel displays a blank instead of an error when the division fails.
The basic IFERROR formula
IFERROR evaluates a value or formula and returns that result if it does not produce an error. If it does produce an error, Excel returns the fallback value you specify. In this case, two quotation marks with nothing between them represent empty text, which makes the cell look blank.
The general pattern is to put the calculation first and the fallback second, separated by a comma. In Microsoft 365 for Windows, commas are the usual argument separators in English-language settings. Excel may use semicolons instead when required by your regional settings; if Excel rejects a comma-separated formula, check the separators used by your installation.
To enter the formula, select the cell that should show the result, type the formula, and press Enter. If you need the same logic for many rows, fill or copy the formula down. Excel adjusts ordinary relative references as it copies the formula, so a formula referring to A2 and B2 will generally refer to A3 and B3 on the next row.
Examples for calculations and lookups
For a division calculation, suppose A2 contains the amount received and B2 contains the number of items. If B2 is zero, division produces a #DIV/0! error. With those values in A2 and B2, use:
=IFERROR(A2/B2,"")
If A2 contains 120 and B2 contains 0, the result displayed is blank. If B2 contains 4, the result is 30. The formula still performs the calculation normally when there is no error; it does not make every result blank.
IFERROR is also often used with lookup formulas. Suppose the lookup key is in E2, the keys are in A2:A100, and the value to return is in the third column of A2:C100. This VLOOKUP returns a blank if the lookup produces an error, such as when the key is not found:
=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"")
The final argument, FALSE, requests an exact match. The dollar signs keep the lookup range fixed when you copy the formula to other rows. Before using this approach, make sure a blank result is suitable: it can hide an unexpected issue in the lookup range or in the data, not just a missing key.
What a blank result means
A formula that returns an empty text string looks empty, but it is not the same as a cell that has never contained anything. The cell still contains a formula. That distinction matters when you use functions or rules that test whether a cell is empty. For example, ISBLANK returns FALSE for a cell containing a formula, even if that formula displays nothing.
Other calculations may treat an empty text result differently from a truly empty cell. A formula that counts numbers will not count the text result as a number, while a formula that counts nonempty values may treat a formula cell as occupied. When a later calculation behaves unexpectedly, check whether the apparently blank cells contain formulas returning empty text.
There is also a visual distinction between a blank-looking result and a genuinely empty cell. Selecting the cell and looking at the formula bar reveals the formula. This is usually a useful trade-off: the calculation remains in place and can update when its inputs change, even when the result is currently hidden.
When IFERROR is the right choice
Use IFERROR when any error from a calculation should be replaced by the same fallback. It can catch errors including #DIV/0!, #N/A, #VALUE!, #REF!, and #NAME?. That broad coverage is convenient for reports and dashboards where an error would be distracting and a blank is an acceptable display.
However, hiding every error can conceal a real problem. A misspelled function, a deleted reference, or an unexpected text value may indicate that a formula needs repair. If the goal is only to hide a missing lookup match, consider using IFNA instead. IFNA replaces the #N/A error but leaves other errors visible, which can make mistakes easier to diagnose.
For a lookup with the same E2 key and A2:C100 range described above, an IFNA version is:
=IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"")
Choose this narrower approach if a missing match is expected but errors such as #REF! should still alert you. Choose IFERROR when all formula errors are intentionally meant to display as blank. Neither choice fixes the underlying error; each only controls what the cell displays.
Leave blank for missing inputs, not just errors
A common surprise is that an empty input does not always cause a formula error. For instance, dividing a number by a blank cell may be treated like dividing by zero and produce an error, but adding a blank to a number can return a valid number. IFERROR only responds to errors; it does not know that an input is incomplete or that you would prefer to wait before showing a result.
If A2 is the amount and B2 is the quantity, and you want no result until both inputs are present, test the inputs before calculating. This version also returns blank when B2 is zero:
=IF(OR(A2="",B2="",B2=0),"",A2/B2)
This formula tests for blank inputs and a zero denominator, rather than relying on an error to signal those cases. It will still show other errors, such as text in a cell where a number is required. That can be helpful when you want to keep unexpected data problems visible. If you instead want to hide those errors too, you can combine the input checks with IFERROR, but do so only when concealing them is appropriate.
Enter and copy the formula in Excel
- Select the cell where the result should appear.
- Type the formula, replacing the example cell references with the references used by your worksheet.
- Press Enter and check the result using both normal inputs and a case that should produce an error.
- To apply it to more rows, use the fill handle at the lower-right corner of the selected cell, or copy and paste the formula down the column.
- Review a few copied rows to confirm that the references shifted as intended and that any lookup ranges stayed fixed.
These steps apply to Excel for Microsoft 365 on Windows. In Excel for Mac, you can enter and copy formulas in the same general way; keyboard shortcuts and some ribbon placement can differ. In Excel for the web, formulas work similarly, though the interface and available actions may vary slightly. If the formula returns a separator or syntax error, check your regional list-separator setting and the number of parentheses and quotation marks.
Common problems and troubleshooting
- The cell still shows an error: Check that the formula itself is wrapped in IFERROR, that the fallback is in the second argument, and that the formula is entered in the cell whose result you are viewing. A separate error in another cell is not hidden by this formula.
- The cell looks blank but another formula counts it: It contains a formula returning empty text, not an actually empty cell. Review how the later formula treats text, formulas, and blank cells.
- Excel rejects the formula: Confirm the commas or semicolons expected by your regional settings, use straight quotation marks, and check that the brackets are balanced.
- A lookup is blank when you expected a result: The lookup may be returning an error that IFERROR hides. Temporarily remove the wrapper or use IFNA to see whether a missing match or another problem is responsible.
- The formula returns zero rather than blank: Zero is a valid calculation result, not an error. IFERROR will preserve it. If zero should also be hidden, add a separate condition for zero rather than expecting IFERROR to handle it.
Display choices and downstream effects
Returning empty text is often preferable to changing Excel’s display settings because it makes the choice part of the formula and applies only to that result. It also preserves error visibility elsewhere in the workbook. But if you need the cell to be genuinely empty—for example, because another process checks whether a cell has no content—a formula cannot both remain in the cell and make the cell truly empty. Consider changing the workflow or placing the calculation somewhere else.
Think about how the result will be used before choosing the fallback. A blank can make a chart or summary easier to read, but downstream formulas may interpret the value differently from zero. In a report, zero can mean a measured result of none, while blank can mean no result is available. Those are different facts. Use a blank only when it communicates the intended meaning, rather than simply making an error disappear.
For troubleshooting, one practical approach is to keep a diagnostic copy of the formula on a working sheet or temporarily remove the error wrapper. That lets you identify the underlying error before deciding whether it is expected. Once the source is understood, restore the presentation formula if a blank is still the right output.
Frequently Asked Questions
How do I make a cell blank if a formula gives an error?
Wrap the calculation in IFERROR and set its fallback to empty text. For example, if A2 is divided by B2, use =IFERROR(A2/B2,""). It returns the calculation when successful and a blank-looking result when it errors.
Does IFERROR hide every Excel error?
It handles errors returned by the expression, including common errors such as #DIV/0!, #N/A, and #VALUE!. That breadth is useful, but it can also hide mistakes you would rather investigate. Use IFNA when only a missing-match #N/A should be replaced.
Why does ISBLANK say FALSE when the cell looks empty?
The cell contains a formula whose result is empty text. It is visually blank, but it is not an unused cell, so ISBLANK does not treat it as truly empty.
Can I leave a cell blank when an input is missing?
Yes. Test the input cells with an IF condition before performing the calculation. This is different from IFERROR because incomplete inputs may not always produce an error; the formula must explicitly decide what to do when an input is missing.
Why does IFERROR still show zero?
Zero is a valid result, so IFERROR returns it normally. Add a separate condition to handle zero if you want zero displayed as blank, and decide whether that would obscure a meaningful result.
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 Formulas & functions
- Excel INDEX Absolute References: When and How to Use ThemYes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you…
- Excel SUBTOTAL Formula Examples for Filtering and TotalsWith sales values in B2:B6, use =SUBTOTAL(9,B2:B6) to add them; for the example values 120, 80, 150, 70,…
- Excel Difference Between Two Numbers: Formula GuideTo find the signed difference between two numbers in Excel, subtract one cell from the other, such as…
- How to Sort and Filter a Table in Excel Step by StepTo sort and filter an Excel table, select a cell in it and use the arrow in a…
- Excel Consolidate Text: Formulas, Flash Fill, and Power QueryTo consolidate text from Excel cells, use TEXTJOIN when you want a separator and control over blank cells,…
- Excel MATCH Function: Syntax, Examples, and Common ErrorsIn Excel, MATCH is a function—not a separate statement—that returns the position of a value in a one-dimensional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.