#REF!, #NAME?, #VALUE!: every Excel error explained and fixed

I have been writing about Excel for six years and I still occasionally have to look up what #NULL! means. So if you've ever opened a workbook to a wall of #REF! cells and felt your stomach drop a little — you're not alone, and you're not bad at this.

Here's the thing nobody tells you up front, though. Each Excel error code means exactly one specific thing. There's no overlap. No mystery. #REF! always means the same thing. #NAME? always means the same thing. Once you can read each one as a one-word diagnosis, fixing the formula takes maybe thirty seconds.

So let's go through them. All of them. With the actual cause and the actual fix.

#REF! — a reference no longer exists

#REF! means a formula is pointing to a cell, range, sheet, or workbook that has been deleted.

That's it. That's the whole meaning.

What triggers it

• You delete a row or column that another formula referenced.
• You delete a sheet referenced by a 3D formula or external link.
• You cut and paste cells in a way that orphans a reference.
• The other workbook in an external link got deleted, renamed, or moved.

Fastest fix

Press Ctrl+Z immediately if you just deleted something. Genuinely, this is your best shot. Once the deletion is committed and saved, you have to rewrite the formula by hand. Excel cannot guess what cell you meant. It tried. It can't.

To find every #REF! in a sheet at once: Home → Find & Select → Go To Special → Formulas → Errors. That selects every error cell. Click each one and look at the formula bar — Excel literally writes #REF! into the formula text where the missing reference used to be. So you'll see something like =SUM(#REF!:B10) and you can figure out what range it should have been.

#NAME? — Excel doesn't recognise the text

#NAME? means Excel can't match a piece of text in your formula to any function name, named range, or table name it knows about.

It's a typo. 95% of the time. Just a typo.

What triggers it

• A typo in a function name. =VLOKUP(...). =SUMIFFS(...) (extra F). =AVERGAE(...). We've all done it.
• A defined name that got deleted. So =SUM(MyRange) errors after MyRange was removed from the Name Manager.
• Text values written without quotes. =IF(A1=Yes, 1, 0) should be =IF(A1="Yes", 1, 0). Excel reads Yes without quotes as a named range called Yes, doesn't find it, gives up.
• A function from a newer Excel version opened in an older version. XLOOKUP in Excel 2016, for instance.
• Missing add-in. A custom function from an add-in that didn't load.

Fastest fix

Click into the formula bar. Look for the misspelled token. Excel auto-capitalises function names as you type — so if your function name is sitting there in lowercase, you spelled it wrong. That's a fast visual tell.

For named ranges that vanished: Formulas → Name Manager to see what currently exists.

#VALUE! — wrong type of input

#VALUE! means a function got handed the wrong data type. Usually text where it expected a number. Sometimes the other way.

What triggers it

• =A1+B1 where one of those cells contains text. Even an apostrophe-prefixed number like '42 is text. Looks like a number, isn't.
• A space character or invisible character — looking at you, CHAR(160), the non-breaking space — in a cell that should be numeric. Pasted from a web page is the classic source.
• A date stored as text being subtracted from a real date.
• An array operation given mismatched array sizes (in pre-Microsoft 365 versions).

Fastest fix

In the cell that errors, click Formulas → Evaluate Formula and step through it click by click. The step where the result becomes #VALUE! is exactly where the bad input lives. This tool is genuinely underused — most people don't know it exists.

To force-convert text to a number, multiply by 1: =A1*1. Or use =VALUE(A1). Both work. To strip non-breaking spaces: =SUBSTITUTE(A1, CHAR(160), "") and then TRIM the result for normal spaces.

#DIV/0! — division by zero or empty

#DIV/0! means division by zero. Or by a blank cell, which Excel treats as zero for arithmetic purposes.

What triggers it

• Literally =A1/B1 where B1 is 0 or empty.
• AVERAGE of an empty range.
• MOD(x, 0) (modulo by zero).

Fastest fix

Wrap the division in IFERROR if a blank result is acceptable:

=IFERROR(A1/B1, 0) returns 0 instead of the error. =IFERROR(A1/B1, "") returns blank.

If you'd rather know the divisor was missing instead of masking it:

=IF(B1=0, "no divisor", A1/B1)

Quick warning. IFERROR hides every error type, not just division-by-zero. So you might mask a #REF! or #NAME? that you'd actually want to know about. IFNA is the safer "hide only #N/A" version when that's all you want.

#N/A — value not found

#N/A means a lookup function failed to find a match.

What triggers it

• VLOOKUP, HLOOKUP, MATCH, XLOOKUP — none of them found the value you asked for in the lookup range.
• Looking up "42 " (trailing space) against "42". They don't match. Excel is strict about this.
• Looking up a number formatted as text against actual numbers. Looks identical to your eye. Different to Excel.
• An exact-match VLOOKUP (4th argument FALSE) where the key isn't in the first column of the table.

Fastest fix

Wrap with IFNA:

=IFNA(VLOOKUP(A1, table, 2, FALSE), "not found")

For trailing-space issues: =VLOOKUP(TRIM(A1), table, 2, FALSE). Make sure the table side is also trimmed.

For text-vs-number mismatches, convert one side. If your lookup value is text but the table has real numbers: =VLOOKUP(VALUE(A1), table, 2, FALSE).

One more thing — #N/A is also intentionally produced by =NA(). Why would anyone do that on purpose? Charts. A cell with #N/A in it gets skipped by chart series, so you can use it as a "no data here" placeholder that doesn't draw a zero. Don't blindly hide it everywhere with IFERROR or you'll break those charts.

#NUM! — invalid numeric input

#NUM! means a function got a numeric input it can't handle.

What triggers it

• =SQRT(-4) — square root of a negative.
• =DATE(10000, 1, 1) — Excel can't represent dates past year 9999.
• =IRR(...) failing to converge on a result within 20 iterations.
• A number too big or too small for Excel's IEEE 754 representation. Rare. The limit is around 1.8 × 10^308. You probably won't hit it doing normal work.

Fastest fix

Validate inputs before passing them in:

=IF(A1<0, "", SQRT(A1))

For IRR non-convergence, give it a starting guess: =IRR(values, 0.1). The default is 0.1 already but explicit is better than implicit, and sometimes a different guess like 0.05 or 0.2 will converge when the default doesn't.

#NULL! — wrong intersection operator

#NULL! is the rarest one. Almost nobody triggers it on purpose. It means you used a space (the implicit intersection operator in legacy Excel) between two ranges that don't actually overlap.

What triggers it

• =SUM(A1:A10 B1:B10) — see that space between the ranges? Excel reads that as "the intersection of these two ranges," which is empty. So, #NULL!.
• Almost always a typo. You meant a comma , for union. Or a colon : to extend the range.

Fastest fix

Replace the space with a comma: =SUM(A1:A10, B1:B10). Done.

If you actually want the intersection of two ranges (legitimate but rare — like you have a row and a column and you want the single cell where they cross), wrap in IFERROR to handle the case where they don't cross.

#SPILL! — a spilled array has nowhere to go

#SPILL! is a Microsoft 365 / Excel 2021+ thing. Only shows up when dynamic arrays are involved.

What triggers it

A formula that returns an array of values needs to "spill" those values into the cells below or to the right. Something is in the way:

• A non-empty cell sits in the spill range. Could be anything. A leftover note. A space character.
• The spill range crosses a merged cell. Merged cells break a lot of things.
• The spill range would extend off the worksheet edge.
• The formula is inside an Excel Table, which doesn't allow dynamic spilling.

Fastest fix

Click the formula cell. Excel highlights the entire intended spill range with a dashed blue border so you can see exactly where it would have landed. Find the obstruction. Clear it.

If you only want a single value rather than the whole array, wrap with an aggregator like =SUM(FILTER(...)). Or use the implicit intersection operator @: =@FILTER(...) returns just the first value.

##### — column too narrow

Five hash symbols in a row. The cell value is fine. The column is just too narrow to show it. (Or, for date and time formats, the value is negative — Excel can't display a negative date.)

Fastest fix

Double-click the column boundary in the column header to auto-fit. Or right-click the column → Column Width → enter a wider value.

For negative dates, change the cell format to a number format and decide whether the negative is what you actually wanted.

#CALC! — calculation engine cannot resolve

#CALC! is newer. Microsoft 365 dynamic-array specific. Means the formula is structurally valid but Excel can't produce a result.

What triggers it

• An empty array result. So =FILTER(A1:A10, A1:A10="zzz") when no cell matches "zzz".
• Nested arrays. Excel doesn't support arrays of arrays.
• A LAMBDA defined recursively without a termination condition. (If you're writing recursive LAMBDAs you probably already know what you're doing.)

Fastest fix

For empty FILTER results, give it a fallback as the third argument: =FILTER(A1:A10, A1:A10="zzz", "no match").

For nested array problems, restructure to avoid producing arrays inside arrays — usually means using MAP or BYROW instead of wrapping array-returning functions inside other array-returning functions.

Summary table — print this

Error	Means	Most common cause	Fastest fix
#REF!	Reference no longer exists	Deleted row, column, or sheet	Rewrite the broken reference
#NAME?	Unrecognised text token	Misspelled function or named range	Fix the typo; check Name Manager
#VALUE!	Wrong data type	Text where a number is expected	VALUE(), TRIM(), or strip CHAR(160)
#DIV/0!	Division by zero	Empty divisor cell	=IFERROR(a/b, 0)
#N/A	Lookup not found	Mismatched key, trailing spaces, text-vs-number	IFNA(...), TRIM, VALUE
#NUM!	Invalid number	Negative input to SQRT, non-converging IRR	Validate input; supply a guess
#NULL!	Empty range intersection	Space instead of comma between ranges	Replace space with comma
#SPILL!	Spilled array blocked	Cell in the way, merged cell, table	Clear the obstruction
#####	Column too narrow	Width less than content	Double-click column boundary
#CALC!	Calc engine cannot resolve	Empty array, nested arrays	Add fallback to FILTER, restructure

So basically

Every Excel error is a precise diagnosis. Not a vague complaint. #REF! is always a deleted reference. #NAME? is always a typo or missing name. #VALUE! is always wrong data type. #DIV/0! is always division by zero. Once you read each error like a one-word answer to "what went wrong" — you stop guessing and start fixing.

Print the table above. Tape it to your monitor. Or screenshot it to your phone. You'll never again squint at #NULL! wondering what fresh hell this is.