Error-Proof a Model with IFERROR

Excel Formulas › Auditing & Error-Proofing

All versionsIFERROR

A single #DIV/0! or #N/A can cascade through a model, breaking every downstream total. Wrapping risky formulas in IFERROR returns a clean fallback — but used wisely, so it hides nothing real.

Quick formula: safe division that won't break totals:
=IFERROR(numerator / denominator, 0)
Return 0 (or a chosen fallback) instead of an error, so a single bad cell doesn't break the whole model.

Functions used (tap for the full reference guide):

IFERRORIFNA

The example

Divide-by-zero returns 0, not #DIV/0!.

AB
1ItemResult
210 / 00
310 / 25

The formula

The formula:

=IFERROR(numerator / denominator, 0) // fallback instead of error

How it works

How it works:

  1. IFERROR(formula, fallback) returns the fallback whenever the formula errors.
  2. Use 0 to keep sums working, "" for a clean blank, or text for a message.
  3. Prefer IFNA when you only want to catch #N/A — so real errors (#REF!, #VALUE!) still surface.
  4. Wrap the whole formula, not just part — and only where an error is genuinely expected.

IFERROR can hide the bug you needed to see. Wrapping everything in IFERROR(…, 0) turns a broken #REF! into a silent zero that quietly corrupts totals. Use it where errors are expected (a lookup that may miss, a divide that may hit zero), and prefer IFNA elsewhere so genuine mistakes still show. Error-proofing should suppress noise, not evidence.

Try it: interactive demo

Live demo

Numerator and denominator.

Result:

Variations

Blank on error

Clean look:

=IFERROR(formula, "")

Catch only #N/A

Let real errors show:

=IFNA(VLOOKUP(...), "Not found")

Flag the error

Surface it:

=IF(ISERROR(formula), "CHECK", formula)

Pitfalls & errors

Don’t hide real bugs. Use IFERROR only where an error is expected; IFNA elsewhere.

Fallback affects math. 0 changes averages and sums differently than a blank.

Wrap the whole formula. Partial wrapping can still let an error through.

Practice workbook

📊
Download the free Error-Proof a Model with IFERROR practice workbook
An error-proofing sheet with the blank, IFNA, and flag variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I error-proof a formula in Excel?
Wrap risky formulas in IFERROR with a fallback: =IFERROR(numerator / denominator, 0) so one error doesn't cascade.
When should I use IFNA instead of IFERROR?
When you only want to catch #N/A (a missed lookup). IFNA lets genuine errors like #REF! and #VALUE! still surface.
What's the danger of IFERROR?
It can hide real bugs by turning errors into silent zeros. Use it only where an error is expected.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: IFERROR handling · IFNA handling · Count errors in a range

Function references: IFERRORIFNA