Errors in Google Sheets and how to fix them

When Sheets cannot work out a formula, the cell shows a code starting with a hash sign. Hover the pointer over the cell and a note explains the cause in a sentence. Read that note first. It is usually precise. This guide lists the codes and their usual cures.

At a glance

Error	Meaning
`#N/A`	a value was not found
`#REF!`	a reference is invalid, or a result cannot spread out
`#VALUE!`	the wrong kind of data
`#DIV/0!`	division by zero or by an empty cell
`#NAME?`	an unknown function or name
`#NUM!`	an impossible number
`#ERROR!`	the formula could not be read at all
`#NULL!`	rare; usually arrives with imported Excel files

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

#N/A

A lookup or a match found nothing.

=VLOOKUP("Z999",A2:E8,2,FALSE)
Result: #N/A
The code is not in the first column.
=FILTER(A2:E8,E2:E8>1000)
Result: #N/A
FILTER found no rows. The note says no matches were found.
=IFNA(VLOOKUP("Z999",A2:E8,2,FALSE),"Not found")
Result: Not found
IFNA replaces just this error.

Check for stray spaces, for numbers stored as text on one side, and for a lookup range that moved when the formula was copied. See IFERROR and IFNA.

#REF!

Three different causes share this code. The hover note tells them apart.

"Array result was not expanded because it would overwrite data." A formula that spills, such as ARRAYFORMULA, FILTER, UNIQUE, QUERY or IMPORTRANGE, needs cells that are not empty. Clear the cells in its way. This is the commonest #REF! in Sheets.
A deleted row, column or tab. The formula now contains #REF! where the address used to be. Undo at once if you can, or retype the reference.
"You need to connect these sheets." An IMPORTRANGE waiting for permission. Hover and press Allow access.

Also: a VLOOKUP index larger than the width of its range, and a circular dependency, where a formula refers to its own cell.

=VLOOKUP("A101",A2:E8,9,FALSE)
Result: #REF!
Column 9 of a five-column range does not exist.

#VALUE!

A function was given data of the wrong kind.

=B2*2
Result: #VALUE!
B2 holds text, which cannot be multiplied.
=DATEDIF("abc",D2,"D")
Result: #VALUE!
Text where a date is needed.

Other causes: a QUERY whose text cannot be parsed (the note quotes the problem), REGEX functions given a number instead of text, and dates that Sheets has read as text because of the locale.

#DIV/0!

=D2/E5
Result: #DIV/0!
E5 is 0.
=IF(E5=0,"",D2/E5)
Result: (blank)
Test the divisor first.

AVERAGE and AVERAGEIF give the same error when there are no numbers to average.

#NAME?

=SUMM(D2:D8)
Result: #NAME?
A misspelt function name.
=IF(E5=0,Sold out,"OK")
Result: #ERROR!
Text without quotation marks. With a space in it, Sheets cannot read the formula at all and reports a parse error.

Causes: a misspelt function, text without quotation marks, a named range that does not exist, or a function from Excel that Sheets does not have.

#NUM!

A number that cannot be: the square root of a negative number, DATEDIF with its dates in the wrong order, or a result too large to hold.

#ERROR!

Particular to Sheets. It means "formula parse error": the formula is not written in a form Sheets can read. Look for:

a missing or extra bracket or quotation mark;
the wrong separator for the spreadsheet's locale, commas where semicolons are needed or the reverse;
two values with nothing between them, as in the example above;
a % or currency sign typed in an odd place.

"Loading..." that does not finish

Import functions and GOOGLEFINANCE show "Loading..." while they fetch data. A few seconds is normal. If it never ends, the usual reasons are too many import formulas in one file, a chain of files importing from one another, or a source that is not answering. Reduce the number of imports, and import from the original source.

A formula shown as text

If the cell displays the formula itself, it was formatted as plain text before you typed, or there is an apostrophe or a space before the equals sign. Set Format, Number, Automatic and enter the formula again. Also check that View, Show, Formulae is not switched on.

Wrong answers with no error

These are the dangerous ones.

A VLOOKUP without FALSE returning a nearby match.
A QUERY silently dropping the text values from a mostly numeric column.
SUM skipping numbers stored as text.
An open-ended ARRAYFORMULA counting blank rows as zeros.

Check totals against a small hand calculation, and compare COUNT with COUNTA on columns that should be all numbers.

Hiding errors

=IFERROR(formula) blanks any error. Use it on formulas you know to be right, for failures you expect. An error you do not understand is information. Fix the cause first.

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

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