How to fix the #NAME? error in Excel
Causes and fixes
- The function name is misspelled
- Text in the formula has no quotes
- A range is missing its colon
- The function doesn't exist in this version of Excel
- A named range was deleted or misspelled
- The function comes from an add-in that isn't loaded
1The function name is misspelled
=VLOKUP(…) or =SUMM(…) isn't a function Excel knows.
How to recognise it
Type the formula in lowercase and press Enter. Names Excel recognises become uppercase; the misspelled one stays lowercase.
Fix
Correct the name. While typing, pick the function from the list that appears and press Tab, so it's always spelled right.
2Text in the formula has no quotes
Text in a formula must be in double quotes. In =IF(A2>100,Over,OK), Excel looks for something named Over and OK.
Fix
=IF(A2>100,"Over","OK")Use straight quotes ("). Curly quotes (“ ”) copied from a document or an email also cause #NAME?.
3A range is missing its colon
=SUM(A2A10) isn't a range, so Excel treats A2A10 as a name it can't find.
Fix
Add the colon: =SUM(A2:A10).
4The function doesn't exist in this version of Excel
XLOOKUP, FILTER, UNIQUE, SORT, LET, LAMBDA, TEXTSPLIT and other newer functions are in Microsoft 365 and Excel 2021 or later. In Excel 2019 or earlier they show #NAME?.
How to recognise it
The file was made in Microsoft 365 and opened in an older version. The formula bar may show _xlfn. before the function name.
Fix
Open the file in Microsoft 365 or Excel for the web, or rewrite the formula with older functions. For example, replace XLOOKUP with INDEX and MATCH:
=INDEX(C2:C100,MATCH(F2,A2:A100,0))5A named range was deleted or misspelled
A formula like =SUM(Sales) needs a name called Sales. If the name was deleted, renamed or misspelled, the result is #NAME?.
Fix
Open Formulas > Name Manager to see the names that exist. Correct the formula, or create the name again with Formulas > Define Name.
6The function comes from an add-in that isn't loaded
Some functions are added by add-ins or by your company's macros. On a computer without that add-in, they show #NAME?.
Fix
Turn the add-in on in File > Options > Add-ins (on a Mac, Tools > Excel Add-ins), or ask whoever made the file which add-in it needs.
Still stuck?
- Select the cell and choose Formulas > Evaluate Formula to see which part Excel can't read.
- Click the warning icon next to the cell and choose Help on this Error or Show Calculation Steps.
- Don't hide #NAME? with IFERROR: it means the formula itself is broken, and hiding it hides the mistake.