Excel formula errors explained: #REF!, #N/A, #DIV/0! and how to fix them

The errors at a glance

#DIV/0! — dividing by zero

You get #DIV/0! when a formula divides by zero or by an empty cell — a margin calculation on a row with no sales, or an average of an empty range. Decide what the result should be when the divisor is zero and say so explicitly:

=IFERROR(A2/B2, 0) also works, but it hides every other error in that formula too, so prefer the explicit check. For averages with conditions, =IFERROR(AVERAGEIFS(...), "") is reasonable because “no matching rows” is the only expected error.

#N/A — a lookup found nothing

#N/A comes from VLOOKUP, XLOOKUP, MATCH and similar functions when the value you’re looking for isn’t in the lookup range. Sometimes that’s correct — the product really doesn’t exist. Often it’s a mismatch you can’t see:

• Extra spaces. "SKU-101 " doesn’t equal "SKU-101". Clean with TRIM on both sides.
• Numbers vs text. 101 (a number) doesn’t match "101" (text). Convert one side, or look up TEXT(A2, "0") or VALUE(A2) to match the other.
• Approximate match by accident. VLOOKUP’s fourth argument defaults to approximate matching. Always end exact lookups with FALSE.

Handle genuine “not found” cases with IFNA, which catches only #N/A:

XLOOKUP has this built in: =XLOOKUP(A2, Prices!A:A, Prices!C:C, "Not found").

#REF! — a reference that no longer exists

#REF! appears when a formula points at cells that were deleted — a row, a column or a whole sheet — or when a lookup asks for a column outside its range:

The range A:C has three columns, so asking for column 4 returns #REF!. Change the 4 to a column inside the range, or widen the range.

If you just deleted something, Ctrl+Z restores it. Otherwise, click the cell and look for #REF! inside the formula itself (for example =SUM(B2,#REF!)), then rebuild that reference. To prevent it, use XLOOKUP or INDEX/MATCH, which don’t rely on a column number, and Excel tables, whose column names survive inserted and deleted columns.

#VALUE! — the wrong type of value

#VALUE! usually means arithmetic on text: =A2+B2 where B2 contains "$1,200" stored as text, "N/A", or a date typed as text. Find the offending cell with Formulas → Evaluate Formula, then fix the data (see converting text to numbers) or make the formula tolerant:

Unlike +, SUM ignores text in cell references, so it won’t error — but check that skipping the text value is really what you want.

#NAME? — Excel doesn’t recognise something

Common causes:

• A typo in a function name, such as =SUMIF typed as =SUMIFF.
• Text without quotes: =IF(A2=Paid, …) should be =IF(A2="Paid", …).
• A named range that doesn’t exist or was deleted.
• A function your version doesn’t have. XLOOKUP, FILTER and UNIQUE return #NAME? in Excel 2016 and 2019.

#NUM! — an impossible number

Examples: the square root of a negative number (=SQRT(-4)), a result too large for Excel, or an iterative function such as IRR or RATE that can’t find an answer. For IRR, supply a guess as the second argument, such as =IRR(B2:B10, 0.1), and check the cash flows include at least one positive and one negative value.

#NULL! — ranges that don’t meet

A space between two ranges is Excel’s intersection operator. =SUM(A1:A10 B1:B10) asks for the cells the two ranges share — none — so it returns #NULL!. You almost certainly meant a comma (both ranges) or a colon (one continuous range):

#SPILL! — something is in the way

In Excel 365, formulas like =UNIQUE(A2:A100) and =FILTER(...) “spill” results into neighbouring cells. If any of those cells already contain something, you get #SPILL!. Click the error, then the warning icon, and choose Select Obstructing Cells to find and clear them. Spilling also doesn’t work inside an Excel table — move the formula outside the table.

#CALC! — an empty array

FILTER returns #CALC! when nothing matches the condition. Give it a value to show instead, using the third argument:

##### — not actually an error

A cell full of hash signs means the column is too narrow to display the number or date. Double-click the right edge of the column header to auto-fit it. If it still shows #####, the cell holds a negative date or time, which Excel can’t display.

How to find every error in a workbook

1. Select all errors on a sheet: press F5 → Special → Formulas, untick everything except Errors, click OK. Every error cell is selected; give them a fill colour so they stand out.
2. Step through them: Formulas → Error Checking moves from one error to the next with an explanation.
3. Trace the source: select an error and choose Formulas → Trace Precedents to draw arrows to the cells it depends on. Errors often start in one cell and spread to everything that refers to it.

Error values are only half the story. Two quieter problems cause more wrong numbers, because nothing looks broken:

• A formula that doesn’t match its column. One row uses =E14*F14 while every other row uses =E14*G14. Excel’s green-triangle warning “Inconsistent formula” catches some of these.
• A number typed over a formula. Someone overwrote a formula with a fixed value, so it stops updating when the inputs change.

MindySheets’ Excel error checker finds all of these in one pass — error values, broken references, inconsistent formulas and typed-in numbers — and Mindy explains each one with a suggested fix you can paste back into Excel.

Frequently asked questions