IFERROR in Excel: Hide Errors the Right Way

Excel IFERROR swaps an error like #DIV/0! or #N/A for something cleaner, such as 0, a blank or a short message. It's one of the most useful functions for tidy reports, and one of the easiest to misuse. This guide shows how to use IFERROR, which fallback to choose, and how to avoid hiding mistakes you actually need to see.

How does Excel IFERROR work?#

IFERROR tries a formula. If the result is normal, you see it. If it's an error, you see your fallback instead.

=IFERROR(value, value_if_error)

IFERROR catches every error type: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. That is both its strength and its risk. It works in Excel 2007 and every later version.

How to fix #DIV/0! with IFERROR#

Excel can't divide by zero, so a division by an empty cell or a 0 gives #DIV/0!. Here the average price is revenue divided by units, and two products sold nothing:

Rows without an error keep their normal result. Only the error rows change.

Choose the fallback carefully:

• 0 is a number, so totals and averages include it. That's fine for "overspend", but misleading for "average price": a price of 0 looks like a real price.
• "" (two double quotes) shows an empty cell. SUM and AVERAGE skip it, which is usually what you want. It is empty text, though, so a formula like =D3+1 on it gives #VALUE!.
• A short message like "New" or "No sales" tells readers why there's no number.

If you know which error you expect, you can test for its cause instead: =IF(C2=0,"",B2/C2). This only handles zero units and still lets any other error show.

How to use IFERROR with VLOOKUP and XLOOKUP#

Lookups return #N/A when the item isn't in the table. IFERROR turns that into a clear message:

=IFERROR(VLOOKUP(B5,$F$2:$H$8,2,FALSE),"Discontinued")

For lookups, IFNA is usually the better choice. It works the same way, =IFNA(value, value_if_na), but replaces only #N/A. If the formula has a real mistake, such as a column number past the edge of the table (#REF!), you still see it. IFNA needs Excel 2013 or later. Read the full IFERROR vs IFNA comparison, or learn why lookups return #N/A and how to fix it.

XLOOKUP (Excel 2021 and Microsoft 365) has its own fourth argument for this, so it needs no wrapper: =XLOOKUP(B2,$F$2:$F$8,$H$2:$H$8,"Check code"). Like IFNA, that argument only covers "not found", so other errors still show.

Why can IFERROR be dangerous?#

IFERROR hides every error, including the ones caused by your own typos. Here is a growth formula that should divide by last year's sales in B2:

=IFERROR((C2-B2)/B22,"New")

B22 is an empty cell below the table, so every row divides by zero and shows "New", even products that sold well last year. No warning appears. Without IFERROR, the #DIV/0! in every row would tell you something was wrong.

Two habits prevent this:

1. Write and check the formula first, without IFERROR. Make sure the normal rows give the right answer.
2. Wrap it last, and only when you know which error you expect and why.

How to show text instead of an error#

The fallback can be any value: a number, text in double quotes, a cell reference or another formula. The growth example shows text for new products:

=IFERROR((C2-B2)/B2,"New")

The brackets around C2-B2 matter. Without them, Excel divides B2 by B2 first, then subtracts, because division comes before subtraction. Format the column as a percentage to see 5% rather than 0.05.

How do you sum a column that contains errors?#

SUM returns an error if any cell in its range holds one, so a single #DIV/0! breaks the total. You have two options.

The first is to fix each row with IFERROR, as above, and then sum the clean column. The second is to skip the errors in the total itself:

=AGGREGATE(9,6,D2:D7)
=SUM(IFERROR(D2:D7,0))

In AGGREGATE, 9 means SUM and 6 means "ignore error values". It works in Excel 2010 and later. The SUM(IFERROR(...)) version works as a normal formula in Microsoft 365 and Excel 2021. In older versions, confirm it with Ctrl+Shift+Enter (Windows) or Control+Shift+Return (Mac).

Both give the total of the rows that worked. Make sure that's what you want: a total that quietly leaves rows out can be as misleading as an error.

What errors should you not hide?#

Some errors are a message about your formula, not your data. Fix these instead of wrapping them:

To check the data before it reaches a calculation, use ISNUMBER, ISTEXT and ISBLANK. And if errors come from conditions that don't match, such as a code with no label, SWITCH has a default value for that: see the SWITCH function.

For every argument and more examples, see the IFERROR function guide.

Practise it#

Try the growth exercise on this page, then practise IFERROR in a live spreadsheet, including the typo that turns every row into "New". After that, practise fixing lookup errors: #N/A, #REF!, hidden spaces, and when to choose IFNA over IFERROR.