#NAME? error in Excel
Excel does not recognize a word in your formula as a function, a name or a cell reference.
What #NAME? means
#NAME? is Excel saying it does not know a word you typed. Every piece of a formula has to be one of a short list of things: a function, a cell reference, a number, a quoted piece of text, or a name you defined. Anything else is an unknown name.
It is almost always a typing problem rather than a data problem, which makes it the easiest error to fix and the most embarrassing to leave in a finished workbook. Because the error appears the moment the formula is entered, you rarely discover it later.
Check the spelling first, then the quotes, then whether any named ranges exist. These four causes account for nearly every case.
Causes of #NAME? and how to fix each
1. A misspelled function name
Excel reads anything that is not a known function, a cell address or a defined name as an unknown name. Type SUMM instead of SUM and the formula fails the moment you press Enter. A useful tell: Excel capitalizes function names you typed correctly when you press Enter, so a name that stays lowercase is not recognized.
| A | B | |
|---|---|---|
| 1 | 5 | #NAME? |
| 2 | 7 | |
| 3 | 9 |
Fix. Retype the function name, or let the formula autocomplete list do it: type the first letters and press Tab to accept the suggestion. Typing =su and picking from the list avoids the typo entirely.
| A | B | |
|---|---|---|
| 1 | 5 | 21 |
| 2 | 7 | |
| 3 | 9 |
SUM adds the three cells.
2. Text in a formula without quotation marks
Text values inside a formula must be wrapped in double quotes. Without them Excel treats the word as a name it should know, and fails. This is the usual cause with IF comparisons and with COUNTIF criteria typed by hand.
| A | B | |
|---|---|---|
| 1 | yes | #NAME? |
Fix. Put the text in double quotes: IF(A1="yes",1,0). Numbers, TRUE and FALSE do not need quotes; words always do.
| A | B | |
|---|---|---|
| 1 | yes | 1 |
The comparison now runs and returns 1 because A1 holds yes.
3. A name that was never defined
A named range such as Sales or TaxRate works only if it exists in the workbook. If the name was deleted, misspelled, or lives in a different workbook, formulas that use it return #NAME?.
| A | B | |
|---|---|---|
| 1 | 100 | #NAME? |
| 2 | 200 |
Fix. Open Formulas, Name Manager and check whether the name is listed. Create it with Define Name if it is missing, or replace it with the real range address. Names are not case sensitive, but they must match letter for letter.
4. A missing colon in a range
Writing A1A3 instead of A1:A3 makes Excel read "A1A3" as one word, which is not a cell, so it reports an unknown name instead of a bad range.
| A | B | |
|---|---|---|
| 1 | 5 | #NAME? |
| 2 | 7 | |
| 3 | 9 |
Fix. Add the colon, or select the range with the mouse so Excel writes the address for you.
Trapping #NAME? in a formula
Do not trap #NAME?. Unlike a lookup miss, it is never a data situation; it means the formula is written wrongly, so IFERROR would hide a formula that will never work. Fix the spelling or the quotes instead.
One exception is a workbook that must open on older versions of Excel where a newer function such as XLOOKUP does not exist. There the error appears on open, and the cure is to use a function the oldest version supports.
#NAME? on Excel job tests
A task may give you a workbook with one or two cells already showing #NAME? and ask you to find and repair them. Look in the formula bar for lowercase function names, missing quotes around words, and range addresses without a colon.
Newer functions are a related trap. If a test environment runs an older Excel, a formula using XLOOKUP, FILTER or SORT shows #NAME? even though it is correct in Microsoft 365. When a function is not available, say so and use the older equivalent.
Related guides
#NAME? questions
- What does #NAME? mean in Excel?
- Excel does not recognize something in the formula as a function name, a defined name or a cell reference. Most often it is a typo or text missing its quotation marks.
- Why does a new function show #NAME? in an older Excel?
- Functions such as XLOOKUP, FILTER and SORT only exist in Microsoft 365 and Excel 2021 and later. An older version has never heard of them and reports
#NAME?. - How do I fix #NAME? in one step?
- Click the cell and read the formula bar. Look for a misspelled function, a word with no quotes, or a name that is not defined, and fix whichever one is there.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample