IFERROR Function
Logical
Replace an error with a value of your choice, and know when IFNA is the safer tool.
Updated 12 Oct 2026 Ā· 4 min read Ā· By Sky Dashboards
Errors in a finished report look broken, even when they mean something harmless like "no sales yet". IFERROR checks whether a formula returns an error and, if it does, shows something else instead. Used carefully, it tidies up a report. Used everywhere, it hides mistakes. This guide covers both sides.
In this guide
IFERROR syntax
=IFERROR(value, value_if_error)| Argument | Required? | What it does |
|---|---|---|
value |
Required | The formula or cell to check. |
value_if_error |
Required | What to return if value is an error: text in quotes, a number, "" for a blank, or another formula. |
If value is not an error, IFERROR returns it unchanged. If it is, you get value_if_error instead.
=IFERROR(C2/B2,0)Returns C2 divided by B2, or 0 if the division fails.
The errors IFERROR catches
IFERROR replaces every one of these:
| Error | Usual cause |
|---|---|
| #N/A | A lookup could not find the value. |
| #DIV/0! | A number was divided by zero or by an empty cell. |
| #VALUE! | The wrong type of value, such as text in a sum like ="ten"+5. |
| #REF! | A reference points to a cell that has been deleted. |
| #NAME? | Excel does not recognise a name: a misspelt function, or text missing its quotes. |
| #NUM! | An impossible number, such as =SQRT(-4), or a calculation that cannot find an answer. |
| #NULL! | A space between two ranges that do not overlap, such as =SUM(A1:A5 C1:C5). |
That breadth is both its strength and its risk. The list mixes errors that mean "no data", such as #N/A and #DIV/0!, with errors that mean "this formula is broken", such as #REF! and #NAME?.
Note
IFERROR cannot hide a #SPILL! error. That comes from cells blocking a dynamic array result, not from the calculation, so clear the cells in the way instead.
IFNA: the narrower, safer option
IFNA works the same way but only catches #N/A. Every other error still shows. Its syntax is =IFNA(value, value_if_na).
=IFNA(VLOOKUP(F2,$A$2:$D$50,3,FALSE),"Not listed")Shows Not listed when the code is missing. A typo such as VLOKUP still shows #NAME?, so you notice it.
For lookups this is usually what you want. A missing code is an expected event, and a friendly label is fine. A misspelt function or a broken reference is a fault, and it should stay visible until you fix it. IFERROR would turn both into Not listed.
IFNA arrived in Excel 2013, so it works in every current version. IFERROR has been around since Excel 2007.
Tip
XLOOKUP has its own if_not_found argument, which behaves like IFNA, so there is no need to wrap it: =XLOOKUP(F2,A2:A50,C2:C50,"Not listed").
Wrap divisions and lookups
Division is the other common case. A creator tracking campaign conversion rates gets #DIV/0! for any campaign with no clicks yet:
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Raw | With IFERROR | |
| 2 | Spring launch | 1,240 | 62 | 5.0% | 5.0% | |
| 3 | Reel giveaway | 860 | 26 | 3.0% | 3.0% | |
| 4 | Story link | 0 | 0 | #DIV/0! | n/a | |
| 5 | Email promo | 415 | 37 | 8.9% | 8.9% |
Column D divides Sales by Clicks directly. Column E wraps the same division in IFERROR, so the campaign with no clicks shows n/a.
For divisions, an IF test is often clearer, because it names the exact condition you expect:
=IF(B4=0,"n/a",C4/B4)Only a zero or blank in Clicks produces n/a. Text typed into Sales by mistake still shows #VALUE!.
Choose the replacement with care. Text such as n/a, or a blank "", makes it obvious there is no result. A 0 looks like a real result and gets included in averages, which can drag a conversion rate down. On a dashboard, a tile that says n/a gets questioned. A tile that says 0% gets believed.
The risk of hiding real errors
IFERROR does not care why a formula failed. Wrap a formula with a typo in IFERROR and you get a tidy, wrong number instead of an obvious #NAME?. Wrap a lookup on a range that someone shortened and the missing rows quietly turn into zeros. The totals still add up. They are just wrong.
A few habits keep IFERROR honest:
- Build and test the formula without IFERROR first. Add it only when the formula works and the remaining errors are the expected kind.
- Prefer IFNA for lookups and an IF test for divisions, so unexpected errors still show.
- Return something visible, such as Check or n/a, rather than 0, unless 0 really is the right answer.
- Add a check cell that counts errors in the raw results, such as
=SUMPRODUCT(--ISERROR(D2:D500)). If it shows more than 0, look before you share the file.
Watch out
Wrapping every formula in IFERROR just in case is the quickest way to send out a report with silent mistakes. Treat each IFERROR as a deliberate decision about one specific, expected error.
ISERROR, ISNA and ISERR
Before IFERROR existed, the pattern was =IF(ISERROR(formula),"",formula), which calculates the formula twice. You will still see it in older workbooks. The IS functions return TRUE or FALSE rather than a replacement value, which makes them useful inside tests and checks:
| Function | Returns TRUE for |
|---|---|
| ISERROR | Any error value |
| ISNA | #N/A only |
| ISERR | Any error except #N/A |
=IF(ISNA(MATCH(A2,Archive!A:A,0)),"New","Repeat")Labels a customer New if their name is not on the Archive sheet, and Repeat if it is.
Frequently asked questions
What is the difference between IFERROR and IFNA?
IFERROR catches all seven error types. IFNA catches only #N/A, which is what a lookup returns when it finds no match. Use IFNA for lookups so that other errors still show.
How do I make IFERROR return a blank?
Use two double quotes, as in =IFERROR(C2/B2,""). The cell holds empty text, so it looks blank but is not truly empty.
Does IFERROR slow down a workbook?
Not noticeably. It calculates the wrapped formula once. The older IF(ISERROR()) pattern calculated it twice, which is one reason IFERROR replaced it.
Why does my IFERROR formula still show an error?
Either value_if_error itself returns an error, or the error is #SPILL!, which comes from blocked cells rather than from the calculation.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā