Confident Cells

IFERROR: showing a message instead of an error

20 November 2026 · 2 min read

IFERROR works out a formula and, if the answer is an error such as #DIV/0! or #N/A, shows something else instead: a blank, a zero or a short message.

A worked example

Each sales rep's average order is their sales divided by their number of orders.

ABCD
1RepSalesOrdersAverage
2Grace£1,200.008£150.00
3Tom£540.006£90.00
4Priya£0.000#DIV/0!
D2 holds =B2/C2, copied down. Priya has no orders, so D4 shows an error.

The error is correct, but it looks broken, and it breaks any total that includes it. It would be clearer to show None.

The formula

=IFERROR(B2/C2,"None")
ABCD
1RepSalesOrdersAverage
2Grace£1,200.008£150.00
3Tom£540.006£90.00
4Priya£0.000None
D2 holds =IFERROR(B2/C2,"None"), copied down.

With lookups

IFERROR is often wrapped round a VLOOKUP to show a message when the value isn't on the list:

=IFERROR(VLOOKUP(A2,$E$2:$G$6,3,FALSE),"Not found")

XLOOKUP has this built in as its fourth part, so it doesn't need IFERROR.

When not to use it

IFERROR hides every error, including real mistakes. If you misspell a function or point at the wrong range, IFERROR shows your friendly message and you never find out. So:

Common mistakes

Putting the message in the wrong place

The formula comes first and the message second. =IFERROR("None",B2/C2) just shows None everywhere.

Text without quotes

=IFERROR(B2/C2,None) gives #NAME? on the rows that had an error, because Excel doesn't know anything called None. Put the message in double quotes.

Using "" and then adding up

A blank from "" is text. SUM ignores it, but a formula like =D2+D3 gives #VALUE!. Show 0 if the cells will be added together.

Try it now

Your turn

In D2, work out each rep's average order (sales ÷ orders). Show None instead of an error for reps with no orders. Your formula will be copied down to D7.

ABCD
1RepSalesOrdersAverage
2Grace£1,200.008
3Tom£540.006
4Priya£0.000
5Marek£2,250.009
6Nia£0.000
7Ollie£980.007

Where it works

IFERROR works in Excel 2007 onwards, Google Sheets and LibreOffice Calc. IFNA works in Excel 2013 onwards and the others.