Skip to content
ArumSoft Learning
KO
    #NAME?Excel

    How to fix the #NAME? error in Excel

    Works in: All Excel versions, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerCheck the function name for a typo. A quick test: type the formula in lowercase. When you press Enter, Excel changes function names it knows to uppercase, so a name that stays lowercase is the one it doesn't recognise.

    Causes and fixes

    1. The function name is misspelled
    2. Text in the formula has no quotes
    3. A range is missing its colon
    4. The function doesn't exist in this version of Excel
    5. A named range was deleted or misspelled
    6. 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.