Excel errors / #NAME?
#NAME? error in Excel
Excel does not recognise a word in the formula.
#NAME? means the formula contains a word Excel does not know. Excel reads every word that is not in quotes as the name of something: a function, or a named range. When it is neither, you get #NAME?.
It is the one error that is always a typing mistake in the formula itself, never a problem with the data. The fix is in the formula bar.
Have a formula that shows #NAME?? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
6 causes of #NAME?, and how to fix each
A misspelled function name
SUMM, VLOKUP, IFEROR. Excel does not guess what you meant.
A B C 1 Price 2 5 #NAME? 3 7 In C2:
=SUMM(A2:A3)→ #NAME?How to fix it. Correct the spelling. To avoid it, start typing the name and pick the function from the list Excel offers: press Tab to accept it.
A B C 1 Price 2 5 12 3 7 In C2:
=SUM(A2:A3)→ 12Text without quotes
In a formula, text must be in double quotes. Without them, Excel takes Paid for the name of a range, finds none, and gives up.
A B C 1 Status 2 Paid #NAME? In C2:
=IF(A2=Paid, 1, 0)→ #NAME?How to fix it. Put the text in straight double quotes. Quotes copied from a web page or a document are often curly (“ ”), and Excel does not accept those.
A B C 1 Status 2 Paid 1 In C2:
=IF(A2="Paid", 1, 0)→ 1A colon missing from a range
A2:A3 is a range. A2A3 is a word, and Excel looks for something with that name.
A B C 1 Price 2 5 #NAME? 3 7 In C2:
=SUM(A2A3)→ #NAME?How to fix it. Put the colon back between the two corners of the range.
A B C 1 Price 2 5 12 3 7 In C2:
=SUM(A2:A3)→ 12A function your version of Excel does not have
XLOOKUP, FILTER, UNIQUE, TEXTJOIN and IFS are newer functions. A file that uses them shows #NAME? in an older Excel, and the formula bar shows the name with
_xlfn.in front, as in=_xlfn.XLOOKUP(...).How to fix it. Open the file in Excel 2021 or Microsoft 365, or rewrite the formula with older functions: INDEX and MATCH for XLOOKUP, nested IF for IFS.
A function name from another language
Excel only understands function names in its own display language. A formula copied from an English tutorial into German Excel (or the other way round) shows #NAME?, because VLOOKUP is called SVERWEIS there.
How to fix it. Translate the formula into the language of your Excel. The separators often change as well: semicolons in place of commas.
A named range that does not exist
=SUM(Sales)works only if a range named Sales has been defined in the workbook. A formula copied from another file leaves its names behind.How to fix it. Define the name (Formulas > Define Name), or replace it with the cells it stood for.
Hiding it on purpose
Do not hide #NAME? with IFERROR. It means the formula is mistyped, and hiding it leaves a formula that can never work.
Common questions
How do I fix #NAME? in Excel?
Look in the formula for a word Excel would not know: a misspelled function, text that is missing its quotes, or a range without its colon. Click in the formula bar and Excel colours the references it recognises; the word left uncoloured is usually the one.
Why does XLOOKUP show #NAME??
Your version of Excel does not have it. XLOOKUP needs Excel 2021 or Microsoft 365. In Excel 2019 and earlier, use =INDEX(C2:C50, MATCH(E2, A2:A50, 0)) to do the same job.
What does _xlfn. mean in a formula?
It marks a function that is newer than the Excel the file is open in. The file stores the function under that prefix, and an Excel that does not know it shows #NAME?. Opened in a recent version, the prefix disappears and the formula works.
Practise it
Functions involved
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- IFS: Checks conditions in order and returns the value for the first TRUE one.
- INDEX: The value at a given row and column of a range.
- MATCH: The position of a value in a row or column.
- SUM: Adds up numbers, cells and ranges.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.