Excel formula errors and how to fix them

When Excel cannot work out a formula, it shows a short code that begins with a hash sign. These codes are not random. Each one names a particular kind of problem, and once you can read them, most errors take a minute to fix. This page goes through them one by one, with a real formula that produces each.

The errors at a glance

Error	Meaning in plain words
`#N/A`	the value you looked for is not there
`#VALUE!`	the wrong kind of data, such as text where a number is needed
`#REF!`	the formula points to cells that no longer exist
`#DIV/0!`	division by zero or by an empty cell
`#NAME?`	Excel does not recognise a word in the formula
`#NUM!`	a number that is impossible or out of range
`#SPILL!`	results need more cells, and something is in the way
`#CALC!`	a dynamic array formula has nothing to return
`#####`	the column is too narrow, or a date is negative

#N/A: not available

A lookup did not find what it was looking for.

=VLOOKUP("Z999",A2:E8,2,FALSE)
Result: #N/A
=MATCH("Z999",A2:A8,0)
Result: #N/A

Causes and cures

The value really is not in the list. That may be useful information, not a fault.
A number stored as text on one side and a real number on the other. See VALUE.
Spaces around the value. See TRIM.
The lookup range moved when the formula was copied, because it had no dollar signs. See cell references.
In VLOOKUP, the value is not in the first column of the table.

To show something friendlier, wrap the formula in IFNA or use XLOOKUP's "not found" argument. See IFERROR.

#VALUE!: the wrong kind of value

The formula was given a type of data it cannot use.

=D5*2
Result: #VALUE!
=FIND("x",A3)
Result: #VALUE!
=VALUE(D5)
Result: #VALUE!

D5 holds the text "12 kg", which cannot be multiplied. The second formula looks for a letter that is not in the cell.

Causes and cures

Arithmetic on a cell that contains text, a space or a unit. Clean the cell, or strip the unit with SUBSTITUTE.
Adding with + across cells where one holds text. SUM ignores text and avoids the error.
FIND or SEARCH did not find the text.
Ranges of different sizes in SUMIFS, COUNTIFS, SUMPRODUCT or XLOOKUP.
A date stored as text used in date arithmetic.

#REF!: an invalid reference

The formula refers to something that is not there.

=VLOOKUP("A101",A2:E8,9,FALSE)
Result: #REF!
=INDEX(A2:E8,20,1)
Result: #REF!

The table has five columns and seven rows, and the formulas ask for column 9 and row 20.

Causes and cures

A row, column or sheet that the formula used has been deleted. The formula then literally contains #REF! in place of the lost address. Press Ctrl and Z at once if you can. Otherwise retype the reference.
A column number in VLOOKUP larger than the table's width.
A position in INDEX outside the range.
A formula copied to a place where its relative reference would fall off the edge of the sheet.

Deleting cells is the dangerous step. Clearing their contents with the Delete key leaves formulas intact. Removing the row or column itself breaks them.

#DIV/0!: division by zero

=D2/E5
Result: #DIV/0!
=AVERAGE(B2:B8)
Result: #DIV/0!

E5 holds 0. The second formula averages a column of text, so there are no numbers to divide by.

Causes and cures

A divisor that is zero or empty, often because data has not been entered yet.
AVERAGE or AVERAGEIF over a range with no numbers or no matching rows.

Test before dividing: =IF(E5=0,"",D2/E5). That says precisely which case you are handling, which IFERROR does not.

#NAME?: Excel does not know that word

Excel met a word that is neither a function, a name nor quoted text.

=SUMM(D2:D8)
Result: #NAME?
=IF(E5=0,Sold out,"OK")
Result: #NAME?

Causes and cures

A misspelt function name, as in the first formula.
Text without quotation marks, as in the second: "Sold out" is needed.
A named range that does not exist, or is misspelt.
A function that your version of Excel does not have. XLOOKUP, FILTER, SORT and UNIQUE need Excel 2021 or Microsoft 365. IFS, SWITCH, TEXTJOIN and MAXIFS need Excel 2019 or later.
A missing colon in a range, such as A2A8.

Typing function names in small letters is a quick test. Excel turns a name it recognises into capitals when you press Enter. One that stays small is misspelt.

#NUM!: an impossible number

=LARGE(C2:C9,20)
Result: #NUM!
=SQRT(-4)
Result: #NUM!
=DATEDIF(DATE(2026,1,1),DATE(2025,1,1),"D")
Result: #NUM!

Causes and cures

Asking LARGE or SMALL for a position beyond the number of values.
The square root of a negative number.
DATEDIF with the later date first.
A result too large for Excel to hold.

#SPILL! and #CALC!

These belong to the dynamic array functions of Excel 2021 and Microsoft 365, such as FILTER, SORT, UNIQUE and SEQUENCE.

#SPILL! means the formula needs to fill several cells and one of them is not empty. Click the cell with the error. A dashed border shows the area it needs. Clear whatever is inside it. Merged cells in the area cause the same error.

#CALC! means the formula has nothing to return, most often a FILTER with no matching rows.

=FILTER(B2:F13,C2:C13="West")
Result: #CALC!
=FILTER(B2:F13,C2:C13="West","No rows")
Result: No rows

Give FILTER its third argument, as in the second formula.

##### is not an error

A cell full of hash signs means the column is too narrow to show the number or date. Widen the column by double-clicking the border at the right of its heading. The value underneath is fine.

Hashes also appear when a date or time works out as negative, for example an end time minus a later start time. Fix the formula.

A circular reference

If a formula refers to its own cell, directly or through other cells, Excel shows a warning and usually a result of 0. =SUM(A1:A10) typed into A10 is the usual case: the total is inside its own range. The status bar at the bottom names a cell involved. Move the formula out of the range it adds.

A wrong answer with no error

The worst errors show no code at all. A total that leaves out the last rows, a lookup that returns the nearest match, an average that skips blanks: all look healthy. A few checks catch most of them.

Compare one or two rows with a calculation by hand.
Check that totals of parts equal the grand total.
Select a range and read the Sum and Count in the status bar.
Use Formulas, Evaluate Formula to watch a formula being worked out step by step.
Press F2 on a suspect cell and look at the coloured outlines.

Hiding errors

IFERROR replaces any error with a value of your choice. Use it on formulas that are known to be right and can fail for a known, harmless reason. Do not use it to silence an error you do not understand. The error is telling you something.

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.