Excel Error Messages Explained: #N/A, #VALUE!, #REF!, #NAME? and Every Other Error

Excel has ten distinct error messages. Each one is trying to tell you something specific — if you know how to read them. This guide covers what each error means, the most common causes, and exactly how to fix (or hide) each one. Bookmark this and stop guessing.

All errors at a glance

#N/A — Not Available

The most common error in real workbooks. Something looked for a value and couldn't find it.

Where it comes from

• VLOOKUP, HLOOKUP, XLOOKUP — lookup value not in the lookup range
• MATCH — search value not in the array
• INDEX/MATCH combos — inner MATCH returns #N/A
• Anywhere you deliberately return NA() as a placeholder

Common causes and fixes

=VLOOKUP(A2, Table, 2, FALSE)   → #N/A

Order ID 1005 (number) doesn't match Order ID '1005' (text)

=VLOOKUP(A2, B:D, 2, FALSE)   trying to look left of the lookup column

"MSFT" vs "msft" — VLOOKUP treats them as equal, but EXACT-based lookups don't

How to catch it

=IFNA(VLOOKUP(A2, Table, 2, FALSE), "Not found")

#VALUE! — Wrong data type

Excel got a data type it wasn't expecting. Numeric functions received text; text functions received errors; date functions got a non-date.

Common causes

=5 + "apple"   → #VALUE!

=A1+A2 where A2 is "$1,234.56" imported as text

=A1*2 fails because A1 has invisible non-breaking spaces from a web copy

=LEN(A1:A10)   → #VALUE! in older Excel

=A1+30 where A1 is "2026-01-15" as text, not a date

The systematic fix

When you get #VALUE!, click into the formula and use F9 on individual parts to see what each returns. Excel highlights the offending part.

#REF! — Broken reference

The formula points at something that doesn't exist anymore.

Common causes

• You deleted a row/column/sheet that a formula referenced
• You pasted a formula into a location that would push its references off the grid
• You moved cells around and broke the linking pattern
• An INDIRECT reference points at a missing sheet or cell

=SUM(A1, #REF!, C1)   ← someone deleted column B

Recovery

• Immediate Ctrl+Z — undo the deletion that caused it, if you just noticed
• Trace precedents — Formulas → Trace Precedents shows what the formula was trying to reference before the break
• Edit the formula — replace #REF! with the correct new reference
• Named ranges — if you named the range instead of using cell addresses, deleting cells within the named range often preserves the formula automatically

Prevention

• Use Excel Tables — they self-adjust when rows/columns change
• Use structured references (Table1[Sales]) instead of cell addresses
• Use named ranges for anything referenced from multiple formulas
• Before deleting, run Find & Replace on the workbook for the cell address you're about to remove — quick sanity check

#NAME? — Unknown name

Excel doesn't recognize part of the formula. Something is spelled wrong, or refers to something that doesn't exist.

Common causes

=SUMM(A1:A10)   → #NAME?
=VLOKUP(A2, ...)   → #NAME?
=IFF(A1>0, ...)   → #NAME?

=IF(A1>0, Positive, Negative)   → #NAME?
=IF(A1>0, "Positive", "Negative")   ← correct

=SUM(Sales)   → #NAME? if there's no named range called "Sales"

=XLOOKUP(...)   → #NAME? in Excel 2016 or older

=SUM(A1 A10)   → #NAME?
=SUM(A1:A10)   ← correct

#DIV/0! — Division by zero

Excel refuses to divide by zero (or by a blank cell, which it treats as zero).

Common causes

• Denominator is literally 0
• Denominator cell is empty (Excel treats blank as 0)
• AVERAGE of an empty range
• Any calculation that boils down to division by zero

Fix

=IF(B1=0, 0, A1/B1)

=IFERROR(A1/B1, 0)

=IF(B1, A1/B1, 0)

#NULL! — Empty intersection

Rare but distinctive. Happens when you use a space between two ranges (Excel's intersection operator) that don't overlap.

=SUM(A1:B5 A2:C10)   → #NULL! if the ranges don't overlap

Actual usual cause

You meant to type a comma between arguments but typed a space instead:

=SUM(A1:A10 C1:C10)   → treats space as intersection operator
=SUM(A1:A10, C1:C10)   ← what you meant

When you see #NULL!, look for a space between things that should be separated by commas or arithmetic operators.

#NUM! — Invalid number

Excel got a number it can't work with — too big, too small, negative when positive was required, or the calculation didn't converge.

Common causes

=10^1000   → #NUM!  (Excel's max is around 10^308)

=SQRT(-9)   → #NUM!
=DATE(-1, 5, 10)   → #NUM!

=RATE(A1, B1, C1)   → #NUM! if the values don't produce a solvable rate

=DATE(2026, 13, 45)   → returns a valid date via rollover, but
=DATE(1899, 12, 31)   → #NUM! (before Excel's date epoch)

#SPILL! — Spill blocked

Modern-Excel error (365, 2021, and newer). A dynamic array formula tried to fill multiple cells but couldn't because the target cells weren't empty.

Common causes

• Data or another formula sitting in the spill path
• Merged cells anywhere the spill would land
• The formula is inside an Excel Table (Tables can't contain spills)
• Circular reference — formula referencing its own spill range

Fix

1. Click the yellow warning icon on the formula
2. Excel selects the obstructing cells for you
3. Clear them, unmerge them, or move the formula elsewhere

For the Excel Table case: dynamic array formulas need to live outside Tables. Move the formula to a regular range.

#CALC! — Calculation problem

Newer error that appears when a modern function can't complete a calculation. Usually means an empty array where content was expected.

Common causes

• FILTER with all-false conditions and no if_empty argument
• UNIQUE of an entirely empty range
• Nested LAMBDA that returns an empty array
• Very complex array operations that Excel can't complete

Fix

=FILTER(A2:A100, B2:B100="West", "No matches")

#GETTING_DATA — Async loading

Not really an error — a status. Excel is still fetching data from an external source (Power Query, real-time data, stock quotes, currency conversions). Wait a moment.

If it stays #GETTING_DATA for a long time:

• Check the data source is responding (Power Query → Refresh)
• Check your internet connection
• For stock/currency: verify the ticker or currency code is valid

Catching errors gracefully

IFERROR — catch everything

=IFERROR(A1/B1, "n/a")

Returns "n/a" for any error. Simple and general.

IFNA — catch only #N/A

=IFNA(VLOOKUP(A2, table, 2, FALSE), "Not found")

Catches only the "not found" case. Other errors (like #REF!) still show — usually a good thing because they indicate real bugs.

ISERROR + IF — for conditional logic

=IF(ISERROR(A1/B1), "Bad data", A1/B1)

Slightly more verbose than IFERROR, but sometimes clearer intent.

Type-specific error detection

Excel has ISERR (all errors except #N/A), ISNA (only #N/A), ISERROR (all errors including #N/A). Use them when your logic depends on the specific error type.

Debugging errors systematically

Step 1: Read the error type

The type tells you the category. #N/A means lookup failed. #REF! means broken reference. Don't just react — think about which category applies.

Step 2: Use F9 in the formula bar

Click into a formula, select a piece of it, press F9. Excel evaluates that piece and shows the result inline. Press Esc to restore. This is the fastest way to find which sub-expression is producing the error.

Step 3: Evaluate Formula

Formulas → Evaluate Formula walks through the calculation step by step, showing each intermediate result. Slower than F9 but more thorough for complex formulas.

Step 4: Trace precedents / dependents

Formulas → Trace Precedents (Ctrl+[) draws arrows to cells the formula reads. Trace Dependents (Ctrl+]) shows cells that reference the current one. Perfect for tracking down where bad data enters.

Step 5: Check data types

Numbers stored as text look identical but behave differently. Format cells → Number type reveals the actual storage. Text-as-number is the source of maybe 40% of all Excel errors.

Step 6: Isolate in a helper cell

Copy the failing formula into a scratch cell. Break it into smaller pieces. Watch each piece's output. The error appears at exactly one step — that's the one to fix.

Google Sheets equivalents

Sheets errors are mostly the same, with a few differences:

Sheets adds one Excel doesn't have: #ERROR! for formula parse errors. Usually a missing bracket, extra comma, or unmatched quote in the formula string.

Frequently asked questions