Fixes / Excel · Checked

Excel #NAME? error: what it means and how to fix it

Short answer

#NAME? means Excel found text in your formula that it doesn't recognize as a function, named range or cell reference. The most common cause is a misspelled function name, such as =VLOKUP instead of =VLOOKUP. Click the cell and check the spelling in the formula bar. Then check that any text in the formula is inside straight double quotes.

What this message means

Excel shows #NAME? in a cell when the formula contains a word it cannot match to anything it knows. That word could be a function name, a defined name (named range or table) or plain text that should have been in quotation marks. The formula itself is still saved. Excel just cannot calculate it. The error also spreads: any other formula that refers to the broken cell shows #NAME? too, so the cell you are looking at may not be where the error starts. If you see _xlfn. in front of a function name in the formula bar, the function is newer than your version of Excel.

Common causes

CauseHow to tell
Misspelled function nameThe formula bar shows something like =VLOKUP( or =SUMIFF(. When you type a correct function name, Excel shows it in a suggestion list. A misspelled one gets no suggestion, and the function name stays lowercase after you press Enter instead of changing to capitals.
Text in the formula without straight double quotesThe formula compares or joins words, for example =IF(A1=Yes,1,0) or =A1&, &B1, with no quotes around Yes or the comma. Curly quotes (“ ”) pasted from Word, Outlook or a web page cause the same error even though they look correct.
Named range is undefined, misspelled or scoped to another sheetThe formula uses a word like =SUM(Sales) and that name is missing from Formulas > Name Manager, is spelled differently there, or has a Scope column that shows a different worksheet instead of Workbook.
Missing colon in a range referenceThe formula has a range with a space or nothing between the cells, such as =SUM(A1 A10) or =SUM(A1A10), instead of =SUM(A1:A10).
Function not available in your version of ExcelThe workbook works on a colleague's computer but not yours, and the formula bar shows _xlfn. before the function, for example =_xlfn.XLOOKUP( or =_xlfn.IFS(. This happens with newer functions such as XLOOKUP, FILTER, UNIQUE, LET, TEXTJOIN and IFS.
Function belongs to an add-in that is turned offThe function is spelled correctly but comes from an add-in (for example EUROCONVERT from the Euro Currency Tools add-in, or a custom function from a company add-in or macro workbook), and the add-in is not ticked in the Add-ins dialog.

How to fix it

1. Correct the function name

  1. Click the cell showing #NAME? and look at the formula in the formula bar.
  2. Compare each function name with its correct spelling, for example VLOOKUP, COUNTIF, SUMIFS.
  3. To avoid typing it yourself, delete the wrong name, type = and the first letters of the function, then press Tab when the right function is highlighted in the suggestion list.
  4. Or select the cell, go to Formulas > Insert Function (or press Shift+F3 on Windows) and pick the function from the list so Excel fills in the correct name and arguments.
  5. Press Enter and check the result.

2. Put text inside straight double quotes

  1. Find every piece of text in the formula that is not a function, cell reference or name, for example Yes, Paid or a space used as a separator.
  2. Wrap each one in straight double quotes: =IF(A1="Yes",1,0) or =A1&" "&B1.
  3. If the formula was pasted from another app, delete any curly quotes (“ ”) and retype them in the formula bar so they become straight quotes (").
  4. Press Enter.

3. Fix range references and defined names

  1. Check every range in the formula has a colon between the first and last cell, for example A1:A10 rather than A1 A10.
  2. Go to Formulas > Name Manager (Ctrl+F3 on Windows) and look for the name the formula uses.
  3. If the name is missing, go to Formulas > Define Name, type the name exactly as it appears in the formula, set Scope to Workbook, choose the cells in Refers to, and select OK.
  4. If the name exists but is spelled differently, correct the formula. To avoid typos, put the cursor in the formula and use Formulas > Use in Formula to insert the name.
  5. If the Scope column shows another sheet, either refer to it as SheetName!Name or delete the name and create it again with Workbook scope.

4. Replace functions your version of Excel doesn't have

Excel 2019, 2016 and older, plus anyone opening a file created in Microsoft 365, Excel 2021 or Excel 2024

  1. Click the cell and look for _xlfn. in front of a function name in the formula bar. That prefix confirms the function is not supported by your Excel.
  2. Check which version you have: File > Account > About Excel on Windows, or Excel > About Excel on a Mac.
  3. Open the file in Excel for the web (sign in at office.com and upload it to OneDrive), which supports the newest functions, or ask the file's author to open it in Microsoft 365.
  4. To make the file work everywhere, rewrite the formula with older functions, for example INDEX and MATCH instead of XLOOKUP, nested IF instead of IFS, or IFERROR in place of IFNA-style logic.
  5. If you only need the values, have someone with a newer version copy the cells and use Paste Special > Values so the results no longer depend on the function.

5. Turn on the required add-in

Windows: File > Options > Add-ins. Mac: Tools > Excel Add-ins.

  1. On Windows, go to File > Options > Add-ins.
  2. At the bottom, set Manage to Excel Add-ins and select Go.
  3. Tick the add-in the function comes from (for example Euro Currency Tools for EUROCONVERT) and select OK.
  4. On a Mac, go to Tools > Excel Add-ins, tick the add-in and select OK.
  5. For custom functions from a macro workbook or company add-in, make sure that file is installed and open, and that macros are enabled for it. Then recalculate with F9.

6. Trace the error back to where it starts

  1. If the formula looks correct, it may be picking up #NAME? from another cell.
  2. Select the cell and go to Formulas > Error Checking > Trace Error (Windows) to draw arrows to the source cell.
  3. On any version, you can also select Formulas > Evaluate Formula and step through the calculation to see which part turns into #NAME?.
  4. Fix the source cell using the steps above. Every cell that depends on it will update automatically.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Copy the formula into a blank cell in a new workbook and rebuild it one function at a time with Formulas > Insert Function, which shows exactly which part Excel rejects. If the file came from someone else, ask them which Excel version they used, since _xlfn. functions only work in Microsoft 365 or recent standalone versions. If the formula uses a company add-in or a custom function written in VBA, contact your IT team or the file's author to get the add-in installed. You can also post the exact formula on the Microsoft Community forum for Excel (answers.microsoft.com).

Questions people ask

How do I hide #NAME? errors in Excel?

You can wrap the formula in IFERROR, for example =IFERROR(your_formula,""), to show a blank instead. This only hides the problem, so fix the underlying typo or missing name first, otherwise the cell will never show a real result.

Why does my XLOOKUP show #NAME? on another computer?

XLOOKUP only exists in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Older versions such as Excel 2019 or 2016 show #NAME? and add _xlfn. to the formula. Use INDEX and MATCH if the file must work in older versions.

What does _xlfn. mean in an Excel formula?

It means the workbook uses a function your version of Excel does not support. Excel keeps the formula but cannot calculate it, so the cell shows #NAME? until the file is opened in a newer version or the function is replaced.

Why does my formula show #NAME? even though the function is spelled right?

Check for text without quotes, curly quotes pasted from another program, a range missing its colon, or a named range that doesn't exist or is scoped to a different sheet. Formulas > Evaluate Formula will show which part fails.

Can a cell show #NAME? because of another cell?

Yes. If a formula refers to a cell that already shows #NAME?, it shows the same error. Use Formulas > Error Checking > Trace Error to find the original cell and fix that one.

Sources

Related errors