When working with Excel formulas, you might come across the #NAME? Error.
Some common reasons for the #NAME? error include misspelled function names, incorrect use of text strings, or improperly defined named ranges.
By carefully reviewing your formula and making the necessary corrections, you can easily resolve this error and go back to working with your data in Excel.
In this article, we will show you some common reasons why the #Name Error occurs, as well as how to fix it.
Understanding #NAME Error
The #NAME error in Excel indicates that there is an issue with the syntax of your formula, often caused by a misspelled function or an incorrect argument.
When you come across the #NAME? error in Excel, it’s important to fix it instead of masking it with error-handling functions like IFERROR. This is because this is not an expected error (such as the DIV error or the REF error). This error is because of a mistake that needs to be corrected.
Below are some of the common reasons why you may see the #NAME error in your worksheet:
- Misspelled function names
- Incorrect cell references or ranges (or worksheet/workbook names)
- Using a function that doesn’t exist in your version of Excel (or using the wrong name of a custom function you have created using VBA)
- Text strings that are not enclosed in double quotation marks
For example, if you’re trying to use the VLOOKUP function but accidentally type “VLOKUP” instead, Excel will return the #NAME error because it doesn’t recognize the function.
Another example is when you use a function like LEN and forget to enclose a text string in double quotation marks, like =LEN(Apple).
In this case, Excel won’t know that you want to refer to the text string “Apple” and will return the #NAME error.
Also read: #VALUE! Error in Excel – How to Fix!
Common Causes of #NAME Error in Excel
Now let’s look at some of the common causes of the NAME error in Excel and how to correct it.
Misspelled Function Names
When entering Excel functions, we have to use the correct spelling of the function name. If we make a typo, we will get a #Name Error.
Sales for three months are shown in the table below. Cell E2 calculates the total sales. But that cell has a #Name Error in it.
If we look at the formula bar, we can see that the function name has a misspelling. The “SUM” function is misspelled as “summ.”
Hence, if we get a #Name Error, we can double-check that we entered the function name accurately.
The following steps can help us avoid making this type of error.
Using IntelliSense to choose the function name
As we start typing the function name, double-click the function name from the Excel functions suggestion list.
Use the Excel Formula Wizard
Alternatively, you can choose the cell into which you wish to enter the function. In this example, it is cell E2. Then, on the “Formula” tab, choose “Insert Function”.
When we click the Insert Function button, a dialog box will appear. After that, we can use the dialog box to search for and choose the function name.
Also read: #NUM! Error in Excel – How to Fix it?
Misspelled Function Arguments
Name errors can also occur when you have misspelled argument names in the functions.
In the following example, the VLOOKUP function is used in cell E2 to get the age of the selected person in cell D2.
But Excel gives a #Name error.
The final argument’s misspelling is the reason for the above problem. “False” should be the final argument. However, the final “e” was missed when typing.
This can be a common issue, so Excel has this inbuilt functionality that displays a selection of options to pick from when we need to enter something other than a cell or range for an argument.
We can double-click and select the option. Typos can happen when typing by hand.
Also read: SPILL Error in Excel – How to Fix?
Using Undefined Names
The use of undefined names in Excel calculations is another very common source of #Name errors.
The table below shows a student’s scores for three subjects. Cell E2 computes the average score of the three subjects. But, there is a #Name error.
In the above scenario, “Scores” is not a defined name in that Excel workbook. Therefore, Excel returns a #Name error.
To overcome this error, we have to follow the below steps.
- Select the cell or the range to define a name.
- Go to the “Formulas’ tab.
- Click “Define Name” in the “Defined Names” group.
- Enter a name in the “Name” box and click “Ok”.
When we click the “OK” button, we will get the average score in cell E2.
Spelling Errors When Using Defined Names
Let’s say that we have a defined name as “Scores” in our Excel workbook.
In the table below, we used the defined name to calculate a student’s average score. Cell E2, however, contains a #Name error.
If we carefully check the name used in the formula, we can see that it does not match the defined name. The formula uses “Score,” but the defined name is “Scores.”
To resolve this issue, we have to match the name entered in the formula to the defined name. In this case, we can correct the name by adding the letter “s” to the end.
Excel will suggest matching defined names in the worksheet as we begin typing the defined name inside the formula. We can just double-click to choose the defined name. This method helps eliminate misspelled defined names in our formulas.
Using Name Range with Local Scope
As we define name ranges, we have to select the scope of the name we are defining.
We can pick “Workbook” (Global scope) or one of the workbook’s sheet names (Local scope) as the scope of the name that we are defining.
A #Name error will be shown by Excel if we try to use a defined name with a local scope in a different worksheet. We cannot change the scope after we have defined the name. So, when defining the name, it is important to choose the scope carefully.
Missing Double Quotation for Text Values
If we want to enter a text value into an Excel formula, we have to enter it inside the double quotation marks.
The invoice numbers of a company are shown in Column A of the table below. Each invoice number has been prefixed with the initials “INV” in column B. Unfortunately, Excel is not giving the expected result, and there is a #Name error.
In this case, the #Name issue happens because the text value – INV is not enclosed by double quotation marks.
Adding double quotation marks before and after the text value will fix the above problem. We can then get the expected outcome.
Missing Colon in Range Reference
When entering a range reference, a colon separates the first and last cell of the range. The missing colon in a range reference results in a #Name error.
The net profit for four quarters of a business is shown in the table below. The total net profit is calculated in Cell F2. However, the result is a #Name error.
The #Name error occurred in the above formula because we failed to include a colon between two cell references in the range reference.
This problem is pretty simple to solve. Simply place a colon between the two cell references.
Then we can fix the #Name error.
Incomplete Cell References
When entering a range reference, make sure that the cell reference format of the top left cell and the lower right cell match.
For example, if we entered the cell reference of the top left cell as a combination of the column letter and row number (e.g., B2), the cell reference of the bottom right cell should also be a combination of the column letter and row number (e.g., D3).
The table below shows a company’s net profit for four quarters. Cell F2 is used to calculate the total net profit of the cell range, beginning with cell B2 and ending with cell E2. But there is a #Name error.
We can see that the range reference’s end cell’s reference format does not match the first cell’s reference format when we check the formula bar. The row number is not entered in the range reference’s last cell reference.
The solution is to match the format of the range reference’s two cell references. So, in this case, we can enter row number (2) for the range reference’s last cell reference.
Opening New Excel Functions in Older Versions of Excel
New functions are occasionally added to Microsoft Excel. Some of the new functions are not compatible with previous versions of Excel.
For example, new functions such as TOCOL, VSTACK, TEXTBEFORE, and TEXTSPLIT are only available and supported in Microsoft 365.
If we open an Excel file containing the above functions in a version of Excel other than Microsoft 365, we will see the #Name error for all cells that use such functions.
If we are sending an Excel file to someone who is using an older version of Excel, we can check and ensure that unsupported functions are replaced with compatible functions.
For example, instead of TEXTBEFORE, consider using the LEFT function.
Use of Functions That Needs to Enable Specific Add-In
Some Excel functions are only available if we enable a specific Add-In.
For example, the EUROCONVERT function, which is used to convert Euros to a Euro member currency and vice versa, is only available if the Euro Currency Add-In is enabled.
Otherwise, Excel will display the #Name problem.
To enable Euro-Currency Add-In, we have to follow the below steps.
- Go to the file tab and click options.
- Select Add-ins from the Excel options dialog box.
- Choose the Excel Add-ins option from the “Manage” dropdown menu and then click on the “Go” button.
- Select “Euro Currency Tools” and click “Ok”.
Preventing #NAME Error in the Future
Now that you’re familiar with fixing the #NAME error, it’s essential to learn some strategies to prevent it in the future.
In this section, let’s explore how to make your Excel experience smoother by preempting the occurrence of the NAME error.
Using Excel’s Formula Autocomplete
When typing a formula, Excel offers formula autocomplete suggestions to help you out.
This helpful feature ensures that you always use the correct function names and reduces the risk of encountering the #NAME error.
Remember to choose the right suggestion to avoid misspelled function names.
Establishing Naming Conventions
Adopting clear and consistent naming conventions for your ranges and tables can make your Excel work more efficient.
Here are some tips for establishing useful naming conventions:
- Use descriptive names to help identify the purpose easily.
- Stay consistent with capitalization and prefixes or suffixes.
- Avoid spaces and special characters in names.
With a structured approach to naming, you can quickly reference ranges and tables in your formulas, minimizing the risk of #NAME errors.
Organizing Add-ins
Ensure you properly set up and organize your add-ins to avoid issues with custom functions. Take a proactive approach by:
- Regularly updating your add-ins.
- Enabling the add-ins you need in Excel.
- Verifying that the function names are correct and unique.
Remember these tips as you work in Excel, and you’ll reduce the likelihood of encountering the #NAME error in the future.
We have discussed a wide range of potential causes for the #Name error problem in Excel in this article. In order to get around the #Name error, you can follow the techniques provided for each cause.
Checking the Variable and Function Names in VBA
If you create user-defined functions using VBA, there is a possibility that you may get the name error when you try to use that function in your worksheet.
In most likelihood, the reason would be a misspelled function name or variable name in the VBA code that is used to create a custom function. Double-checking all the variable and function names and the syntax in the VBA code should take care of this issue.
Other Excel articles you may also like:
