#NAME? means Excel does not recognise a word in your formula. Most often the function name is misspelled: =SUMM(B2:B6) instead of =SUM(B2:B6). The other causes are text without quotation marks, a range without its colon, a name that was never defined, and a function your version of Excel does not have.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | Total | Lookup | |
| 2 | Apple | 1.2 | #NAME? | #NAME? | |
| 3 | Pear | 1.5 | |||
| 4 | Plum | 0.8 | |||
| 5 | Bread | 2.4 | |||
| 6 | Milk | 1.1 |
#NAME? Excel does not know a name in this formula. Check the spelling of the function.Click D2 and correct SUMM to SUM: the total appears (7). Then fix VLOKUP in E2 to VLOOKUP, and it returns Pear's price, 1.5.
A quick way to spot a typo in Excel: type function names in lowercase. When you press Enter, Excel turns names it knows into capitals (=sum(b2:b6) becomes =SUM(B2:B6)). A name that stays lowercase is one Excel does not know. Picking functions from the list that appears as you type (press Tab to insert one) avoids most typos.
#NAME? in an IF formula: text without quotes
Text inside a formula must be in straight double quotation marks. Without them, Excel reads Pass as the name of something and cannot find it.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Without quotes | With quotes |
| 2 | Ana | 72 | #NAME? | Pass |
| 3 | Ben | 45 | Fail | |
| 4 | Cy | 88 | Pass | |
| 5 | Dee | 50 | Pass |
#NAME? Excel does not know a name in this formula. Check the spelling of the function.C2 shows #NAME?. D2 has the same formula with quotes and works for every student. The same rule applies to text criteria: =COUNTIF(A2:A6,North) is #NAME?, =COUNTIF(A2:A6,"North") counts. Numbers, cell references and TRUE/FALSE never take quotes (see the IF function).
Curly quotes cause the same error. A formula copied from a web page, an email or Word often has “Pass” instead of "Pass". Excel does not treat the curly ones as quotes:
=IF(B2>=50,“Pass”,“Fail”) #NAME?
=IF(B2>=50,"Pass","Fail") Pass
Delete each curly quote and type it again in Excel.
A range without its colon
B2B6 is not a range, it is a word, and Excel looks for a name called B2B6.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | Total | |
| 2 | Jan | 120 | #NAME? | |
| 3 | Feb | 95 | ||
| 4 | Mar | 140 | ||
| 5 | Apr | 110 |
#NAME? Excel does not know a name in this formula. Check the spelling of the function.Click D2, add the colon so the formula reads =SUM(B2:B5), and it returns 465.
A name that was never defined
A formula can use a name such as TaxRate in place of a cell, but only after the name exists. If it was never created, was deleted, or is spelled differently, the formula returns #NAME?.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Price | With a name | With a cell | Tax rate | |
| 2 | Desk | 240 | #NAME? | 288 | 20% | |
| 3 | Chair | 85 | 102 | |||
| 4 | Lamp | 40 | 48 |
#NAME? Excel does not know a name in this formula. Check the spelling of the function.C2 refers to TaxRate, which does not exist in this workbook. D2 points at F2 instead and works: 288 for the desk. The $ keeps F2 fixed when the formula is filled down.
To create the name in Excel, select F2, type TaxRate in the Name Box (the box above column A, before the formula bar) and press Enter, or use Formulas > Define Name. Formulas > Name Manager lists every name in the workbook, where it points, and names that point at #REF! because their cells were deleted. A name also has a scope: one defined for Sheet1 only gives #NAME? on Sheet2.
A function that is too new for your Excel
Excel shows #NAME? for any function it does not have. The file opens fine, but every formula using a newer function fails. In the formula bar, Excel 2019 and earlier show such functions with a _xlfn. prefix:
=_xlfn.XLOOKUP(E2,A2:A6,B2:B6) #NAME? in Excel 2019
=INDEX(B2:B6,MATCH(E2,A2:A6,0)) works in every version
| Function | Needs |
|---|---|
| IFS, SWITCH, MAXIFS, MINIFS, CONCAT, TEXTJOIN | Excel 2019 or later |
| XLOOKUP, XMATCH, FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, LET | Excel 2021 or Microsoft 365 |
| TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, TAKE, DROP | Excel 2024 or Microsoft 365 |
| REGEXTEST, REGEXEXTRACT, GROUPBY, PIVOTBY | Microsoft 365 only |
If the people who open your file use an older Excel, stick to the older functions, or send the results as values (Copy, then Home > Paste > Values). Excel for the web has the newest functions. Google Sheets knows most of them under the same names. Functions from add-ins (EUROCONVERT from Euro Currency Tools, or a company add-in) also give #NAME? when the add-in is not enabled: turn it on under File > Options > Add-ins (on a Mac, Tools > Excel Add-ins).
Fix a COUNTIF that shows #NAME?
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | North orders | |
| 2 | 1001 | North | 250 | ||
| 3 | 1002 | South | 120 | ||
| 4 | 1003 | East | 310 | ||
| 5 | 1004 | North | 90 | ||
| 6 | 1005 | North | 140 | ||
| 7 | 1006 | West | 75 |
Your turn: The formula =COUNTIF(B2:B7,North) returns #NAME? because North has no quotes. Write a working version in E2 that counts the North orders.
When the text you are counting is in a cell, refer to the cell and drop the quotes: =COUNTIF(B2:B7,G1) with North in G1. A reference never needs quotes, and the formula keeps working when G1 changes.
Frequently Asked Questions
What does #NAME? mean in Excel?
Excel found a word in the formula that is not a function, a defined name or a cell reference. The usual causes are a typo in the function name (=SUMM(B2:B6)), text without quotation marks (=IF(B2>50,Pass,Fail)) and a function the installed Excel version does not have.
Why does XLOOKUP give #NAME? in Excel?
XLOOKUP needs Excel 2021 or Microsoft 365. Excel 2019 and earlier do not know it, show #NAME?, and display the formula as =_xlfn.XLOOKUP(...). Use =INDEX(C2:C6,MATCH(E2,A2:A6,0)) there instead.
Why do I get #NAME? when the formula is correct?
Look for quotation marks copied from a web page or a word processor. Curly quotes like “Pass” are not text delimiters in Excel, so the word between them is read as a name. Retype the quotes in Excel as straight quotes "Pass".
How do I find all #NAME? errors in a workbook?
Press Ctrl+G (Control+G on a Mac), click Special, choose Formulas, leave only Errors ticked and click OK. Excel selects every formula cell with an error on the active sheet; repeat on each sheet. Formulas > Name Manager > Filter > Names with Errors lists defined names that no longer work.
