- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Web App, Excel Starter 2010.
This guide will outline why the #NAME? error appears in your formulas and how to rectify this.
The single greatest reason why the #NAME? error displays in your formula is due to a typo being found in the formula name. Check the following example:
Important: The #NAME? error emphasises that an element within the syntax must be rectified. Therefore, once you notice the error in your formula, fix it immediately. Resist the temptation to use any error-handling functions like IFERROR to conceal the error.
Use the Formula Wizard in Excel to evade formula names from getting typos. After you begin entering a formula name in a cell or the Formula Bar, a list of formulas corresponding to your exact search terms are presented in a dropdown. When you input the formula name and the opening parentheses, the Formula Wizard shows the syntax as hover text.
You can even apply the Function Wizard to bypass the syntactical errors. Pick the cell including the formula. Then, on the Formula tab, press Insert Function.
Excel will instantly launch the Wizard for you.
As you select each argument, Excel will provide you with the relevant information for each one.
Listed below are alternative causes of the #NAME? error.
The formula refers to a name that has not been defined – results in the #NAME? error
Once your formula has a reference to a name that is undefined in Excel, you will notice the #NAME? error.
Look at the following example of a SUM function, with the data subject being Profit, which is an uncategorised workbook name.
Solution: Use Name Manager to define a number. Next, relay the name to the formula. This can be achieved by following these steps:
- If you already have the data in the spreadsheet, and want to categorise a name to particular cells or a cell range, start by choosing the cells within the spreadsheet. Conversely, if you seek to produce a new range, you can jump to the next step.
- Press Define Name, and then click Define Name. Do this in the Formulas tab, within the Defined Names group.
- Type a unique name.
- For the Scope, pick if you prefer the name to solely be available inside the sheet. Alternatively, you can confirm the whole workbook.
- Type an optional comment.
- Press OK. The next part is to place the name in the formula.
- Leave the cursor in the formula syntax at the stage where you wish to relay your newly created name.
- Select the Formulas tab, in Defined Names group. Then, click Use in Formula. After this, pick your target defined name that you seek to add.
For further information on using defined names, see Define and use names in formulas.
The formula has a typo in the defined name – causes the #NAME? error
If the syntax mistakenly refers to a defined name, you will notice the #NAME? error.
Carrying on with the past example, a defined name for Profit was formed in the spreadsheet. In the following example, the name is misspelt which results in the function coming up with the #NAME? error.
Solution: Fix the syntax typo and attempt running the formula again.
Tip: Rather than physically typing defined names in formulas, you can configure Excel to conduct intuitively for you. Do the following: visit the Formulas tab, in Defined Names group, then select Use in Formula. Next, pick your preferred defined name to insert. Excel will assign the name to the formula.
The syntax is missing double quotation marks for text values – causes the #NAME? error
After you embody text references in formulas, you must contain the text in quotation marks, irrespective of if you’re solely using a space. If the syntax ignores double quotation marks “” for a text value, you will notice the #NAME error. Have a look at the following example:
The syntax in this example has omitted double quotation marks for has; that’s why there is an error in the first place.
Solution: Return to your syntax and manually check to guarantee that any text values are enclosed with quotation marks around them.
A colon was omitted in a range reference
If you forget a colon in a range reference, the formula will present a #NAME? error.
In the following example, the INDEX formula shows the #NAME? error because the B2 to B12 range has excluded a colon.
Solution: Inspect your syntax to check that all range references contain the colon.
You are using a function that requires a specific add-in that is not enabled in Excel
There are some Excel functions that work conditionally on the basis that specific add-ins are activated. Otherwise, utilising these functions will generate a #NAME? error. For example, to apply the EUROCONVERT function, the Euro Currency Tools add-in must be toggled on. If you employ custom functions or macros that need the Analysis ToolPak, ensure that the Analysis ToolPak add-in is activated.
To activate add-ins in Excel:
- Choose File > Options.
- Select Add-ins.
- In the Manage list box, click Excel Add-ins and press Go.
- Tick the relevant box and press OK.
Do you have a specific function question?
Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.