IFERROR: useful safety net, risky hiding place

IFERROR makes a sheet look tidy, but an unexpected calculation error and a normal missing result are not the same thing. Before adding a fallback, decide which kind of failure is acceptable.

IFERROR is a presentation decision after diagnosis. In the text row it catches a different problem from division by zero. Returning the same zero for both errors makes totals and exception counts difficult to interpret. During development, show the raw A4/B4 calculation to inspect the error.

Enter revenue in A2:A4 and units in B2:B4. The third row has a text problem on purpose.

Distinguish a failed calculation from a zero result

Formula syntax

=IFERROR(value, value_if_error)

What goes into it

A quick example first

=IFERROR(10/0,"Check")

=IFERROR(XLOOKUP(A2,IDs,Prices),"Not found")

Before you copy this into a real sheet

If zero units are an expected case, handle that condition directly with IF(B2=0,”No units”,A2/B2). Other errors can then remain visible. For lookup misses, prefer IFNA or XLOOKUP’s missing-match argument when you want to catch only #N/A. Keep an exception column if a clean dashboard must use friendly messages. Do not assume a workbook is accurate because its visible error codes have disappeared.

Useful habits

• Debug the inner formula before hiding its error.
• Use IFNA when only a missing-match #N/A condition is expected.
• Choose a fallback that makes missing data visible rather than silently converting every error to zero.

Where people get caught out

• Wrapping an untested formula in IFERROR and hiding a broken reference.
• Using a blank fallback when downstream users need to know that data is missing.

Other functions worth a look

A detail worth remembering

While building a workbook, let unexpected errors remain visible. Only replace an error when you know what it means and what the next person should see instead.