How to Fix Common Excel Formula Errors (#REF, #VALUE, #N/A)

Excel errors look alarming but each one is a specific message telling you what's wrong. Once you know the vocabulary, fixing them is quick. Here are the five you'll meet most often.

#DIV/0! — dividing by zero or a blank. Excel can't divide by zero. This usually appears when a denominator cell is empty or contains 0 — common in early-month reports before data is entered. Fix it by guarding the division:

=IF(B2=0,"",A2/B2)

or wrap it: =IFERROR(A2/B2,"").

#VALUE! — wrong type of data. You're doing math on something that isn't a number, often a number stored as text or a stray space. ="5"+3 works, but =A2+B2 fails if A2 contains text. Find the offending cell, convert it to a number (multiply by 1, or use VALUE()), and clean stray spaces with TRIM().

#N/A — not found. A lookup (VLOOKUP, MATCH, XLOOKUP) couldn't find the value. The data genuinely isn't there, or there's a formatting mismatch (text vs number, trailing spaces, different capitalization in some cases). Verify the value exists in the lookup column, then handle missing matches gracefully:

=IFERROR(VLOOKUP(A2,Data!A:C,3,FALSE),"Not found")

#REF! — a broken reference. A cell the formula pointed to was deleted, or you copied a formula somewhere that pushed a reference off the sheet. This one can't be auto-guessed because the original target is gone — you have to point the formula at the correct cell again. To prevent it, avoid deleting rows/columns that formulas depend on, and use absolute references ($A$1) where appropriate.

#NAME? — Excel doesn't recognize a name. Almost always a typo in a function name (=VLOKUP(...)), a missing quotation mark around text, or a reference to a named range that doesn't exist. Check spelling and quotes first.

The universal safety net: IFERROR. Wrapping a formula in IFERROR catches any error and returns your chosen fallback:

=IFERROR(your_formula, "fallback value")

Use it on reports and dashboards so a single empty cell doesn't fill the sheet with red. But don't over-use it — hiding an error you don't understand can mask a real data problem. Diagnose first, then suppress.

A quick diagnosis routine. When a cell errors: read the error type, click the cell to see which reference is highlighted, check the data types of the cells involved, and confirm the values you expect actually exist. Nine times out of ten that locates the cause.