IFERROR and IFNA
Updated · Works in: Excel 2013+, Microsoft 365, Google Sheets · Every example checked before release
Short answer
=IFERROR(value, fallback) returns the fallback when the value is any error, e.g. =IFERROR(C2/B2,0). Prefer IFNA around lookups (it only catches #N/A), and testing the cause with IF when you know it.
IFERROR replaces an error with something friendlier. It's useful, and easy to overuse: an error is often telling you something real. Here's how to use it well.
The problem
Two campaigns haven't started, so their clicks are 0 and the conversion divides by zero. The #DIV/0! errors also break anything that uses these cells, like an average.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Conversion | Owner |
| 2 | Spring email | 1200 | 36 | 3.0% | Mara |
| 3 | 860 | 12 | 1.4% | ||
| 4 | Display ads | 0 | 0 | #DIV/0! | Jonas |
| 5 | Search ads | 2300 | 69 | 3.0% | Priya |
| 6 | Newsletter | 540 | 27 | 5.0% | |
| 7 | Partner blog | 0 | 0 | #DIV/0! | Kenji |
IFERROR(value, fallback)
IFERROR returns the value when it's fine and the fallback when it's any error. Here the fallback is 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Conversion | Owner |
| 2 | Spring email | 1200 | 36 | 3.0% | Mara |
| 3 | 860 | 12 | 1.4% | ||
| 4 | Display ads | 0 | 0 | 0.0% | Jonas |
| 5 | Search ads | 2300 | 69 | 3.0% | Priya |
| 6 | Newsletter | 540 | 27 | 5.0% | |
| 7 | Partner blog | 0 | 0 | 0.0% | Kenji |
Often better: test the cause
IFERROR also hides typos and broken references. When you know what causes the error, test for it directly. This version shows an empty cell only when Clicks is 0; any other problem still shows up.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Conversion | Owner |
| 2 | Spring email | 1200 | 36 | 3.0% | Mara |
| 3 | 860 | 12 | 1.4% | ||
| 4 | Display ads | 0 | 0 | Jonas | |
| 5 | Search ads | 2300 | 69 | 3.0% | Priya |
| 6 | Newsletter | 540 | 27 | 5.0% | |
| 7 | Partner blog | 0 | 0 | Kenji |
Blank cells: ISBLANK
ISBLANK(E2) is TRUE only for a truly empty cell, so IF(ISBLANK(E2), "Needs owner", E2) fills the gaps. E2="" also catches formulas that return empty text.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Conversion | Owner | Owner check |
| 2 | Spring email | 1200 | 36 | Mara | Mara | |
| 3 | 860 | 12 | Needs owner | |||
| 4 | Display ads | 0 | 0 | Jonas | Jonas | |
| 5 | Search ads | 2300 | 69 | Priya | Priya | |
| 6 | Newsletter | 540 | 27 | Needs owner | ||
| 7 | Partner blog | 0 | 0 | Kenji | Kenji |
IFNA for lookups
IFNA(value, fallback) catches only #N/A, the “not found” error of lookups, and lets every other error through. That's usually what you want around a VLOOKUP.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | Monstera | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | Terracotta pot | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | Watering can | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | Fern | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | Potting mix | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | Monstera | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | Ceramic pot | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-999 | 1 | Unknown SKU |
Check yourself
One real exercise from the “Decisions” unit. Checked on our server; no account needed.Work out each campaign's conversion (Sales in C ÷ Clicks in B) in D2, and leave the cell empty ("") when Clicks is 0. Test the clicks with IF instead of catching the error.
- Use IF
- Without IFERROR
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Conversion | Owner |
| 2 | Spring email | 1200 | 36 | Mara | |
| 3 | 860 | 12 | |||
| 4 | Display ads | 0 | 0 | Jonas | |
| 5 | Search ads | 2300 | 69 | Priya | |
| 6 | Newsletter | 540 | 27 | ||
| 7 | Partner blog | 0 | 0 | Kenji |
Common mistakes
Wrapping every formula in IFERROR. A typo or a deleted column then shows a calm 0 instead of a warning.
Leaving out the second argument:
IFERROR(A1/B1)isn't valid.Using IFERROR around a lookup when IFNA would do: IFERROR also hides a wrong column number (
#REF!).
Questions
What's the difference between IFERROR and IFNA?+
IFERROR catches every error type. IFNA catches only #N/A, so other mistakes stay visible. IFNA needs Excel 2013 or later; Google Sheets has both.
How do I show a blank instead of an error?+
Use an empty string as the fallback: `=IFERROR(A1/B1,"")`.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.