#NAME? error in Excel: what it means and how to fix it

#NAME? means Excel read a word in your formula that it doesn’t recognise. Apart from cell references, every word in a formula must be something Excel knows: a function such as SUM or IF, a name given to a range or table, TRUE or FALSE, or text inside double quotes. Anything else, and the formula shows #NAME?. The fix is to find that word, and the formula bar shows it to you.

1. A misspelt function

=SUMM(B2:B4) or =VLOKUP(E2,A2:C6,3,FALSE): Excel has no function by that name, so it can’t work the formula out. Correct the spelling.

To avoid it, type the first letters after = and pick the function from the list Excel shows, with Tab. Or type function names in lower case: when you press Enter, Excel turns every name it recognises into capitals, so =sum(b2:b4) becomes =SUM(B2:B4). If you typed in lower case, a function name still in lower case is the misspelt one.

2. Text without double quotes

Text inside a formula needs double quotes. Without them, Excel reads Pass and Fail as names, finds no range with those names, and shows #NAME?:

C2fx=IF(B2>=50,Pass,Fail)

Row	A	B	C
1	Student	Score	Result
2	Ana	72	#NAME?

Put each word in straight double quotes:

Watch out for curly quotes (“Pass”) pasted from a word processor or a chat: Excel only accepts the straight “ kind.

3. A range without its colon

A range is written with a colon between its first and last cell. Without it, B2B4 looks like a name, not a range:

D2fx=SUM(B2B4)

Row	A	B	C	D
1	Month	Sales		Total
2	Jan	1200		#NAME?
3	Feb	950
4	Mar	1430

Put the colon back:

4. A name that isn’t defined

=SUM(Sales) works only if the workbook has a range called Sales. If the name was never created, was deleted, or is spelt differently, the formula shows #NAME?. Open Formulas › Name Manager to see the names that exist, and pick them from Formulas › Use in Formula instead of typing them.

5. A function your Excel doesn’t have

Newer functions such as XLOOKUP, FILTER and UNIQUE exist only in Excel 365 and Excel 2021 or later. Open a file that uses them in an older version and the formula bar shows _xlfn. in front of the function’s name; the cells keep their last saved result until the formula recalculates, then show #NAME?. Nothing is wrong with the formula; that version can’t run it. For files shared with older versions, use functions they have, such as INDEX and MATCH. A function from an add-in, such as EUROCONVERT, also shows #NAME? until its add-in is turned on.

Practise it: Pass or Fail, with the quotes

Your task: In C2, show Pass when the score in B2 is 50 or more, and Fail otherwise.

Graded live

C2fx

Row	A	B	C
1	Student	Score	Result
2	Maya	49

Write the formula in the bar above, then press Check or Enter.

Hint

IF takes a test, the result when it's true, and the result when it's false. Put both words in double quotes.

Solution

=IF(B2>=50,"Pass","Fail") Any formula that returns the same result passes.

Check

Don’t hide it

IFERROR can turn #NAME? into a blank or a 0, but that only hides a formula that can never work. Unlike #N/A, which can be a normal “not found”, #NAME? always means something in the formula is wrong: fix it instead.