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

  1. A misspelled function name

    SUMM, VLOKUP, IFEROR. Excel does not guess what you meant.

    ABC
    1Price
    25#NAME?
    37

    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.

    ABC
    1Price
    2512
    37

    In C2: =SUM(A2:A3) → 12

  2. Text 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.

    ABC
    1Status
    2Paid#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.

    ABC
    1Status
    2Paid1

    In C2: =IF(A2="Paid", 1, 0) → 1

  3. A colon missing from a range

    A2:A3 is a range. A2A3 is a word, and Excel looks for something with that name.

    ABC
    1Price
    25#NAME?
    37

    In C2: =SUM(A2A3) → #NAME?

    How to fix it. Put the colon back between the two corners of the range.

    ABC
    1Price
    2512
    37

    In C2: =SUM(A2:A3) → 12

  4. A 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.

  5. 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.

  6. 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

Other errors