Catch Any Formula Error With IFERROR

Excel & Google Sheets

How it works

IFERROR runs your calculation and, only if it fails, returns the fallback instead — and it catches every error type: #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, and #NUM!. Returning 0 is the popular fallback for numeric columns because a 0 flows cleanly into every SUM and running total below it, so one bad row no longer breaks the report. The trade-off is that a fallback 0 looks exactly like a real zero, so for columns people read directly, many prefer "" to keep missing data visible. When the only error you expect is a failed lookup, reach for IFNA instead — it catches #N/A but lets genuine bugs like #REF! stay loud. That selectivity is the point: IFERROR silences everything, including the errors you needed to see.

A2/B2

The calculation to try. Any formula can go here — a division, a VLOOKUP, a percentage change.

0

What to show when it errors. Use 0 for columns that feed math, "" for display columns, or a label like "Check qty".

When to use it

Use it where errors are expected and harmless: revenue per order when an order count can be zero, VLOOKUPs against a customer list that's still being filled in, month-over-month change when last month had no sales.

Common mistakes

• ✕Wrapped the whole workbook in IFERROR and shipped wrong numbers.

  ✓IFERROR doesn't fix errors — it hides them. A broken reference or a mistyped range quietly becomes 0 and flows straight into your totals. Only wrap formulas where you know exactly why the error happens and the fallback is genuinely the right answer.

• ✕Returning 0 when the data is actually missing.

  ✓A fallback 0 keeps SUM working, but "no data yet" and "really zero" now look identical. If people read the column directly, return "" or "n/a" instead, and keep 0 only for columns that feed calculations.

• ✕Using IFERROR when you only expect #N/A from a lookup.

  ✓=IFNA(VLOOKUP(D2,Prices!A:B,2,FALSE),0) handles the missing-item case but still surfaces a real #REF! or #VALUE! so you can fix it instead of averaging it away.