Yes. In Excel for Microsoft 365, wrap a formula in IFERROR and use an empty text value as the fallback; Excel then displays a blank-looking result when that formula returns an error. For example, if the numerator is in A2 and the denominator is in B2, use =IFERROR(A2/B2,"").
What IFERROR does
IFERROR evaluates an expression and returns the expression’s result if it is not an error. If the expression produces an error, it returns the alternative value you specify. That alternative can be text, a number, a reference, or an empty text value. The empty text value written as "" is what makes the cell appear blank.
Excel errors can arise for different reasons. Dividing by zero returns #DIV/0!; a formula that refers to a missing or misspelled function can return #NAME?; and a lookup may return #N/A when it cannot find a match. Rather than letting one of these error messages appear, IFERROR can show a chosen fallback.
The function does not repair the underlying calculation. It changes what the formula displays when an error occurs. That distinction matters: a blank-looking result may mean the calculation succeeded and returned an empty string, or that the calculation failed and IFERROR concealed the error. Keep the cause in mind when reviewing or sharing a workbook.
Wrap an existing formula
To use the function, preserve your original calculation as the first argument and add ,"" before the final parenthesis. If a formula already occupies a cell, edit it in the formula bar or directly in the cell. Add the wrapper around the complete existing formula, rather than replacing its references with a new calculation.
For instance, if quantity is in A2 and unit price is in B2, and you want a line total that looks blank if the calculation errors, use =IFERROR(A2*B2,""). If the multiplication works, Excel displays the calculated total. If it returns an error, the cell displays no visible text.
When the formula is ready, press Enter. 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 into the other cells. Relative references such as A2 and B2 normally adjust for each row. References with dollar signs, such as $A$2, remain fixed when copied.
Examples for common formula errors
Division by zero is a frequent reason to use a blank fallback. If revenue is in C2 and the number of units is in D2, the following formula displays revenue per unit when D2 is nonzero and a blank-looking result when division produces an error:
With revenue in C2 and units in D2, use =IFERROR(C2/D2,""). If C2 contains 120 and D2 contains 4, the result is 30. If D2 is zero, the result appears blank.
Lookup formulas are another common case. Suppose product IDs are listed in A2:A100, product names are in B2:B100, and the ID to find is in E2. With that layout, use =IFERROR(VLOOKUP(E2,$A$2:$B$100,2,FALSE),""). If the ID exists, the formula returns the corresponding name from the second column of the lookup range. If no exact match is found, it suppresses the resulting error and shows a blank-looking value.
For a more specific example with a newer lookup function, suppose IDs are in A2:A100, names are in B2:B100, and the requested ID is in E2. In Microsoft 365, use =IFERROR(XLOOKUP(E2,A2:A100,B2:B100),""). XLOOKUP is available in Microsoft 365 and Excel 2021 or later. A broad error wrapper can be useful, but it can also hide an unexpected problem in the lookup formula itself, so check the source ranges if results are unexpectedly blank.
Blank-looking result versus a truly empty cell
A formula returning "" does not make its cell truly empty. The cell still contains a formula, and Excel treats the returned value as zero-length text in many contexts. This differs from a cell that has never had a value or formula entered in it.
The distinction can affect other formulas and spreadsheet features. For example, a formula that checks whether a cell is empty may treat a formula returning "" differently from a genuinely empty cell. A count of numeric values will not count the empty text as a number, while a count of nonempty values may treat a formula cell as occupied. Sorting, filtering, charts, and data validation can also behave differently depending on whether the cell contains a formula, text, or no content.
If a downstream calculation requires a true empty cell, a formula cannot literally erase its own contents. You may need to adjust the downstream formula to recognize the empty-text result, or use a separate process that clears cell contents. Avoid assuming that a cell which looks blank is equivalent to an unused cell.
When IFNA is a better choice
IFERROR hides every error type produced by its first argument, not just the error you expected. That may conceal a broken reference, an incorrect function name, or another issue that should be fixed. If the only expected problem is a lookup returning #N/A because no match exists, IFNA is a narrower option. It returns the fallback only for #N/A and leaves other errors visible.
Suppose IDs are in A2:A100, names are in B2:B100, and the requested ID is in E2. In Microsoft 365 or Excel 2021 and later, use =IFNA(XLOOKUP(E2,A2:A100,B2:B100),""). A missing ID produces a blank-looking result, while a different error can still alert you to a problem. Choose this approach when you want missing matches to be quiet but want other formula failures to remain detectable.
You can also return a useful message instead of a blank. For the same lookup layout, use =IFNA(XLOOKUP(E2,A2:A100,B2:B100),"Not found"). The message makes the reason for the missing result more apparent to a person reading the sheet. In reports where empty output is a deliberate design choice, an empty string may be more suitable; in workbooks used for checking or data entry, a visible message is often clearer.
Choose a fallback that fits the worksheet
A blank display is not always the best way to handle an error. Choose the fallback based on what a reader needs to know and how other formulas use the result. Consider these common options:
- Empty text: Use
""when the result should look clean in a report and the error is an expected condition, such as a lookup value that may not exist yet. - An explanatory message: Return text such as “Check input” when someone needs to investigate or correct the data.
- A numeric value: Return 0 only when zero is an accurate and safe substitute. Zero can affect averages, totals, charts, and decisions, so it should not be used merely to make an error disappear.
- The original error: Leave the formula unwrapped, or handle only a specific error with
IFNA, when errors are useful signals during review.
For financial, operational, or scientific work, hiding every error may make a sheet look more complete than it is. A report can look polished while containing missing calculations. Consider showing an explanatory message during data preparation, then using blank output only in a final presentation where the missing result is understood and documented.
Common problems and troubleshooting
If the cell still shows an error, check that IFERROR surrounds the entire expression. A missing closing parenthesis or an error left outside the wrapper can prevent it from catching the intended result. Also check whether the cell is displaying the formula as text rather than calculating it. A leading apostrophe, text-formatted cell, or Show Formulas view can make a formula appear instead of its result.
If the result appears blank but you expected a number, temporarily remove the wrapper or change its fallback to a visible message. This can reveal whether the expression is failing. Also inspect the inputs: a source cell may be empty, contain text instead of a number, or have an unexpected space. The wrapper can hide the visible error, but it cannot make invalid source data valid.
When entering formulas in Excel for Microsoft 365 on Windows, commas are the standard argument separators in the examples here. Some regional settings use semicolons instead, depending on the system’s list separator. If Excel rejects a formula with commas, check the regional settings or use the separator expected by that installation. On Mac, the function works the same way; menu and keyboard details may differ, but no special Mac version of IFERROR is needed.
Do not confuse an error with an ordinary blank input. If a formula returns a result such as zero or an empty string without an error, IFERROR will not replace it. It responds only when the expression evaluates to an Excel error. If you want blank output when an input cell is empty, use a condition that checks the input separately rather than expecting IFERROR to handle it.
Frequently Asked Questions
How do I make an Excel cell blank if a formula gives an error?
Wrap the existing formula in IFERROR and use "" as its second argument. For example, if A2 is divided by B2, use =IFERROR(A2/B2,""). The cell looks blank when that calculation returns an error.
Does IFERROR delete the error or fix the formula?
No. It changes the displayed result by substituting the fallback value. The underlying problem may still exist, so inspect the formula and its inputs if the blank result is unexpected.
Is a cell containing IFERROR and an empty string actually empty?
No. It still contains a formula that returns zero-length text. Some formulas and spreadsheet features distinguish that from a genuinely empty cell.
How can I show blank only when a lookup has no match?
Use IFNA around the lookup and return "". Unlike IFERROR, IFNA handles only #N/A, so other errors remain visible.
Why is my cell blank even though I expected a result?
The formula may be returning an error that the wrapper hides, or its calculation may be returning an empty value. Temporarily remove the wrapper or replace the empty fallback with a message to identify what is happening.
Related
- Subscript in Google Sheets: Methods That Work
- Subtract Dates in Excel in Years: Two Reliable Methods
- Excel Strikethrough Shortcut: How to Cross Out Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query Nested If: Syntax and ExamplesIn Power Query, write a nested conditional as if condition1 then result1 else if condition2 then result2 else…
- Excel FILTER Function for Text: Examples and TipsWith records in A2:C6 and the text to match in B2:B6, use =FILTER(A2:C6, B2:B6="North", "No matching rows") to…
- SUMPRODUCT in Excel: What It Contains and How It WorksExcel’s SUMPRODUCT contains one required array argument and optional additional arrays; it multiplies corresponding entries across the arrays,…
- Excel Formula for Fiscal Year: Step-by-Step GuideThe simplest Excel formula for determining a fiscal year is =YEAR(A2)+(MONTH(A2)>=FiscalStartMonth), where A2 contains the date and FiscalStartMonth…
- Excel COUNTIF Remove Duplicates: How to Count Unique ValuesThe short answer: Excel’s COUNTIF function does not remove duplicates, but you can combine it with other functions…
- Excel INDEX MATCH Lookup: Formula, Examples, and TipsIn Microsoft Excel for Microsoft 365, INDEX and MATCH work together to look up a value: MATCH finds…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.