IFERROR Return Blank: Hide Errors With an Empty Cell
By ExcelReady TeamUpdated 6 min read
The short answer
To make IFERROR return a blank cell, use two double quotes as the second argument: =IFERROR(B2/C2,""). If the formula works, you see its result. If it gives any error, such as #DIV/0! or #N/A, the cell looks empty. The "" is an empty text string, so the cell isn't truly blank to other formulas.
Key takeaways
- =IFERROR(formula,"") shows the result when it works and an empty-looking cell when it fails.
- Two double quotes with nothing between them is an empty text string, not a space.
- A cell showing "" is not truly empty: ISBLANK says FALSE, COUNTA counts it, and adding 1 to it gives #VALUE!.
- SUM and AVERAGE skip "" cells, so totals of an IFERROR column still work.
- For lookups, IFNA(formula,"") is safer because it hides only #N/A, not every error.
On this page+
To make IFERROR return blank instead of an error, put two double quotes as its second argument: =IFERROR(B2/C2,""). When the formula works, you see the answer. When it fails with #DIV/0!, #N/A or any other error, the cell looks empty. This guide shows the pattern with division and VLOOKUP, explains why that "blank" can still trip up other formulas, and when to show a 0 or a message instead.
How to make IFERROR return a blank cell#
IFERROR has two arguments: the formula to try, and what to show if it fails.
=IFERROR(value, value_if_error)To show nothing on an error, make value_if_error an empty text string: two double quotes with nothing between them, "". Here is a store list where some stores had no visits:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Store | Revenue | Visits | Per visit |
| 2 | North | 1,200 | 300 | 4 |
| 3 | South | 900 | 0 | |
| 4 | East | 1,500 | 250 | 6 |
| 5 | West | 0 | 0 | |
| 6 | Central | 2,000 | 400 | 5 |
A plain =B2/C2 filled down shows #DIV/0! in rows 3 and 5, because you can't divide by zero. In D2, type this instead and fill it down:
=IFERROR(B2/C2,"")Rows 2, 4 and 6 show 4, 6 and 5. Rows 3 and 5 look empty. The IFERROR function guide covers the syntax, and the guide to fixing the #DIV/0! error explains other ways to handle division by zero.
IFERROR with VLOOKUP: return blank when the item isn't found#
The most common use is a lookup that returns #N/A when an ID is missing. Wrap the whole lookup in IFERROR:
=IFERROR(VLOOKUP(F2,A2:C6,3,FALSE),"")If F2 holds a store that isn't in the list, the cell stays empty instead of showing #N/A. This keeps reports clean when some IDs haven't been added yet. The article on IFERROR with VLOOKUP goes through more lookup cases.
For lookups, IFNA is usually the better wrapper:
=IFNA(VLOOKUP(F2,A2:C6,3,FALSE),"")IFNA hides only #N/A, the "not found" error. If the range is broken and returns #REF!, you still see it. The comparison of IFERROR vs IFNA explains when each one fits. IFNA works in Excel 2013 and later; IFERROR works in Excel 2007 and later.
Why "" is not a truly blank cell#
A cell that shows "" looks empty, but it holds a formula that returns empty text. Other formulas treat it differently from a cell that was never filled in.
| Check on D3 (shows "") | Result | Why |
|---|---|---|
=ISBLANK(D3) | FALSE | D3 contains a formula |
=D3="" | TRUE | The result is empty text |
=COUNTA(D2:D6) | 5 | COUNTA counts the "" cells |
=COUNTBLANK(D2:D6) | 2 | COUNTBLANK counts "" as blank |
=D3+1 | #VALUE! | You can't add 1 to text |
=SUM(D2:D6) | 15 | SUM skips text |
=AVERAGE(D2:D6) | 5 | AVERAGE skips text |
So totals and averages of an IFERROR column work fine. Problems appear when another formula does maths on a single cell, such as =D3*12. Use =SUM(D3)*12 or =N(D3)*12 to treat the blank as 0, or test it first with =IF(D3="","",D3*12).
To check whether an IFERROR cell looks empty, test =D3="", not ISBLANK.
Blank, 0 or a message: which should IFERROR return?#
A blank isn't always the best choice. Pick the fallback based on what the reader needs:
- Return "" (blank) when a missing value is normal and expected, like stores with no visits yet. It keeps the report tidy.
- Return 0 when the cell feeds other calculations:
=IFERROR(B2/C2,0). Arithmetic keeps working. But 0 can mislead, because "no data" and "zero per visit" are not the same thing. - Return a message such as
"No visits"or"Not found"when someone needs to act on the gap. A blank can hide a problem that should be fixed.
If you aren't sure, a short message is the safest choice in a shared file. Blanks are best for dashboards and printed reports.
IFERROR blank vs a lookup that returns 0#
Don't confuse two similar problems. IFERROR hides errors. A lookup that finds the item but returns an empty cell doesn't give an error: it shows 0. IFERROR can't help there, because 0 is not an error.
To turn those zeros into blanks, test the result instead:
=IF(VLOOKUP(F2,A2:C6,3,FALSE)="","",VLOOKUP(F2,A2:C6,3,FALSE))The guide on returning a blank instead of 0 in VLOOKUP covers this in full, including a version that handles both cases.
Blank results and charts#
Charts read "" as text, and a line chart plots text as 0. So a line can drop to zero for every month that IFERROR hid. If you want a gap or a straight line across instead, return NA() for the chart's data: =IFERROR(B2/C2,NA()). Excel doesn't plot #N/A values in line charts. You can still hide the #N/A in the table with conditional formatting.
Common mistakes with IFERROR and blanks#
These slips cause most problems:
- Typing a space between the quotes.
" "returns a space, not an empty string. The cell looks blank, but=D3=""is FALSE and TRIM is needed to clean it. - Wrapping too much. IFERROR hides every error, including typos in your own formula. Build the formula first, check it works, then wrap it.
- Hiding errors that matter. A blank where a price should be can lead to a wrong invoice. Use a message when someone must act.
- Using ISBLANK to test the result. It returns FALSE for "" cells. Use
=D3="".
Practise it#
IFERROR is short, but knowing when to hide an error is the real skill. Practise IFERROR in a live spreadsheet: you replace #DIV/0! with a clean fallback, wrap a lookup and see when hiding errors backfires, with instant feedback on every formula. Start with the exercise on this page, then read the full IFERROR guide for more examples.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
In D2, divide Revenue by Visits, but return a blank instead of an error when Visits is 0. Use =IFERROR(B2/C2,""). Row 2 has visits, so you'll see the result.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Per visit | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 |
Tip: The first argument is the division B2/C2. The second is two double quotes with nothing between them.
Practise this in a lesson
Frequently asked questions
How do I get IFERROR to return a blank?
Use an empty pair of double quotes as the value_if_error argument: =IFERROR(B2/C2,""). The cell shows the result when there is no error and looks empty when there is one.
Why does my IFERROR blank cell cause a #VALUE! error?
The "" result is text, so arithmetic such as =D3+1 fails with #VALUE!. Use SUM instead, as in =SUM(D3,1), which skips text, or return 0 instead of a blank.
Is a cell with "" blank in Excel?
It looks blank but it isn't empty. ISBLANK returns FALSE and COUNTA counts it, although COUNTBLANK does count it as blank. Test for it with =D3="" instead.
Should I use IFERROR or IFNA to return a blank?
For lookups, use IFNA so only a missing item shows as blank and other errors, like a broken range, still appear. Use IFERROR when any error should be hidden, such as a division by zero.