The #NAME? error appears in Excel when it cannot recognize text in a formula. This typically happens because of a misspelled function name or a reference to a named range that does not exist. This article explains the specific causes of this error and provides clear steps to find and correct the problem in your spreadsheet.
Key Takeaways: Fixing the #NAME? Error
- Formula Auditing > Error Checking: Use this tool to jump directly to cells containing the #NAME? error for quick review.
- Formula AutoComplete: Typing = and the first few letters of a function prevents spelling mistakes by letting you select from a list.
- Ctrl + F to search for #NAME?: This keyboard shortcut helps you locate all instances of the error in a large worksheet.
Why Excel Shows the #NAME? Error
Excel displays the #NAME? error when it encounters text within a formula that it cannot interpret as a valid function, defined name, or reference. The software checks the text against its internal library of functions and your workbook’s list of named ranges. If no match is found, Excel cannot calculate the formula and shows this error instead of a result. The most frequent triggers are simple typographical errors in common function names, like typing =VLOKUP instead of =VLOOKUP. Another common cause is deleting or renaming a cell range that was previously defined with a name, which leaves formulas that reference that old name broken.
Other Less Common Causes
The error can also appear when using a function from the Analysis ToolPak, like =EDATE, without having the add-in enabled. Text strings within a formula that are missing their surrounding double quotation marks may also be misinterpreted as a name. For example, =IF(A1>10, Yes, No) will cause a #NAME? error because “Yes” and “No” are not in quotes and Excel tries to find them as named ranges.
Steps to Correct Misspelled Functions and Undefined Names
Follow these steps to systematically find and fix the source of the #NAME? error in your workbook.
- Use Error Checking to locate the error
Go to the Formulas tab on the ribbon. In the Formula Auditing group, click Error Checking. If a #NAME? error exists, a dialog box will appear. Click ‘Show Calculation Steps’ to see which part of the formula is failing, then click ‘Edit in Formula Bar’ to correct it. - Check for misspelled function names
Click directly on the cell with the error. Look at the formula in the formula bar. Compare the function name to Excel’s correct spelling. Use Formula AutoComplete as you type corrections: after typing = and the first few letters, press Tab to insert the correct function name from the dropdown list. - Verify named ranges exist
If the formula uses a name like =SUM(SalesData), you must check if ‘SalesData’ is defined. Go to the Formulas tab and click Name Manager. Look for the name used in your formula. If it is missing, you need to redefine the named range or replace the name in the formula with the actual cell reference. - Ensure required add-ins are enabled
For functions like =EDATE or =ACCRINT, go to File > Options > Add-ins. At the bottom, select ‘Excel Add-ins’ from the Manage dropdown and click Go. In the dialog box, check the box for ‘Analysis ToolPak’ and click OK. - Add missing quotation marks to text
In formulas that output text, ensure all text strings are enclosed in double quotes. Change =IF(A1>10, Yes, No) to =IF(A1>10, “Yes”, “No”) to resolve the error.
If the #NAME? Error Persists After Checking
Sometimes the error is not in the cell you are examining but is linked to another problem in the workbook.
Error Appears After Copying a Sheet from Another Workbook
Formulas that use named ranges defined in the original workbook may show #NAME? when copied. The name is not brought over with the sheet. Open the Name Manager. For any undefined name, either create a new named range in the current workbook or edit the formula to use standard cell references.
Formula References a Deleted Sheet Name
A formula like =SUM(Sheet2!A1:A10) will show #NAME? if ‘Sheet2’ was deleted. Excel treats the deleted sheet name as an undefined name. You must restore the deleted sheet from an earlier version or rewrite the formula to reference existing sheets.
Using a New Function in an Older Excel Version
Functions like =XLOOKUP or =FILTER do not work in Excel 2019 or earlier. The formula will show #NAME? when opened in those versions. Replace the function with an older compatible alternative, like using =INDEX and =MATCH instead of =XLOOKUP.
Common Error Sources and Their Solutions
| Item | Misspelled Function | Undefined Named Range |
|---|---|---|
| Primary Cause | Typographical error when entering a formula | Name was deleted or never defined |
| Quick Fix | Use Formula AutoComplete when typing | Create or redefine the name in Name Manager |
| Prevention Method | Double-check function spelling before pressing Enter | Avoid deleting named ranges used in formulas |
| Audit Tool | Formula Auditing > Show Formulas (Ctrl + `) | Formulas > Name Manager for a full list |
| Example | =SUMM(A1:A10) causes #NAME? | =TotalSales causes #NAME? if ‘TotalSales’ is undefined |
You can now identify and fix the two main causes of the #NAME? error in Excel. Remember to use the Name Manager to audit all defined names if errors appear after deleting data. For a related issue, learn how to use the IFERROR function to display a custom message instead of an error code. A useful advanced tip is to press F3 while editing a formula to insert a defined name from a list, ensuring you never misspell a range name.