How to Fix the #NAME? Error in Google Sheets

By Gerard Fernandez · Updated · 2 min read

#NAME? appears when a formula contains a word that Google Sheets doesn't recognize. It's almost always a typing mistake, and the tooltip tells you which word is the problem.

What the error looks like

In our test, the total of a price column should have been =SUM(C2:C5), but it was typed as =SUMM(C2:C5):

Google Sheets cell C6 showing #NAME? with the tooltip Error: Unknown function: 'SUMM', and the formula =SUMM(C2:C5)
Real test: the message names the unknown word, "Unknown function: 'SUMM'."

There are two versions of the message. "Unknown function" means the word before a bracket isn't a function. "Unknown range name" means a word without brackets isn't a range: usually text that's missing its quotes (cause 2).

The four usual causes

1. A misspelled function

SUMM, VLOKUP, COUNTIFS written as COUNTIF S… Correct the spelling. To avoid it, type = and the first letters, then pick the function from the suggestion list with Tab.

2. Text without quotation marks

Text inside a formula must be in double quotes. In our test, =IF(C2>20, Expensive, "Cheap") gave #NAME? because Sheets took Expensive for the name of a range:

Google Sheets cell D2 showing #NAME? with the tooltip Error: Unknown range name: 'EXPENSIVE', and the formula =IF(C2>20, Expensive, "Cheap")
Real test: "Unknown range name: 'EXPENSIVE'." Sheets shows the word in capitals.

Add the quotes and it works (D2 then showed Expensive):

=IF(C2>20, "Expensive", "Cheap")

Two related cases give a different error. Text with a space and no quotes, like Very expensive, and curly quotes (“ ”) copied from a website or a document both showed #ERROR! with "Formula parse error" in our test, not #NAME?. Retype the quotes in Sheets. See how to fix Formula parse error.

3. A named range that doesn't exist

If the formula uses a named range such as =SUM(Sales), check Data → Named ranges. The name may have been deleted, renamed or misspelled.

4. A range missing its colon

=SUM(C2C4) reads as a name, not a range: in our test it showed "Unknown range name: 'C2C4'". Add the colon: =SUM(C2:C4).

Frequently asked questions

Can I hide #NAME? with IFERROR?

You can, but you shouldn't. In our test, =IFERROR(SUMM(C2:C4), 0) quietly showed 0, a wrong total with no warning. #NAME? always means the formula is written wrong, so hiding it just hides a bug.

Why does a formula from Excel give #NAME? in Sheets?

A few functions exist in only one of the two programs. Check the Google Sheets function list (Help → Function list) for an equivalent.

What other errors should I know?

See #VALUE!, #N/A, #REF! and #DIV/0!.