This error might occur if either:
There is a typo in the formula (the most common cause),The range used is not valid,A colon or quotations mark is missing, etc.
The reason for the error may vary from case to case and there is not a single size that may fit all. To better understand the concept, have a look at the image below in which Excel is showing a #name error in cell B11 and the formula used in su(B3:B10). As we all know, there is no Excel function su, so, Excel threw the #name error. Now again, have a look at the image above and check the cell B12 which shows the Sum of the range B3 to B10, and the formula used is sum(B3:B10). So, by comparing the cells B11 and B12, you can note that the formula in the B11 cell has a typo where the formula should be SUM but due to the typo, it is written as su, thus causing the #name error. Keep in mind this article is written for Excel but the steps and methods discussed in it cover a majority of the spreadsheet programs like Google Sheets, which also have a #name error.
Reasons for the #Name Error in Excel
You may encounter the #name error on Excel due to many reasons and you may go through these reasons to rectify the #name error on your Excel sheet. These reasons can be categorized as:
Fixes for #Name Error in Excel with Examples
As you have understood the basics of the #name error, let’s discuss each scenario in detail and then the steps to fix it. Keep in mind that after understanding a particular scenario, have a good look at your formula and try to rectify it accordingly. If you still cannot find the issue, then you may use the Formula Assistant, Formula Wizard, or Name Manager to clear the error (discussed later). But before we move on with the steps and methods to rectify a #name error in Excel, make sure the language of the problematic sheet (like French) or Excel matches the locale language of your system (e.g., English USA) as mismatching languages can sometimes trigger the #name error in Excel.
1. #Name Error due to Typo in the Formula
Before we try other steps, let us make sure the formula or function we entered is correct and it does not have a typo. Let us clears this through an example. Moreover, if you are trying to use a formula or function (like Total), that is not an Excel formula or function, then that could result in the #name error.
2. #Name Error Due to an Incorrect Range
As you have confirmed in the previous step that the formula is correct, now let’s ensure that the range entered is correct (if a range is being used).
3. #Name Error Due to a Missing Colon
If the range you have defined in your formula is missing a colon, then that could result in the #name error as the Excel will fail to apprehend it as a range and will try to interpret it as a function, which it is not, thus the #name error. Let us understand this by the example below:
4. #Name Error Due to Missing Quotations Around a Text String
If your formula uses a text and you forgot to add double-quotations around the text in the formula, then that could result in the #name error as the Excel “thinks” the text as a function or range which is not present. Let us clear the concept by the example below: Keep in mind that the double-quotes are different from smart or curly quotes, as the smart or curly quotes around a text may cause the #name error. This can happen as if you are copying a formula from another application (like MS Word) as it automatically replaces the straight double-quotes with the smart or curly quotes. You can see it in the image below: The rectification is to edit the formula in Excel and replace the smart or curly quotes with straight double-quotes.
5. #Name Due to Range Name not Defined
Excel provides us the feature of naming ranges and then that Range name can be referred to in a formula. If you are referring to a Range Name in your formula but the name is not correct, then that could result in the #name error. To better understand it, let’s take the following example: Keep in mind that you can also use Formulas» Use in Formula» All Points to view the named ranges.
6. #Name Error Due to the Local Scope of the Range
You may encounter the #name error in Excel if the Range name you are trying to use has a local scope, not global. When you name a range, by default it is global i.e., you can access in it any worksheet of an Excel workbook. If a Range name is set to be of the Local Scope, then that Range can be only referenced in the worksheet where it is defined, not in any other worksheet of the workbook and if you try to use the range name of that sheet in any other sheet, then that may result in the #name error in Excel. Let us understand the concept through the following example: Keep in mind that you can simply check a range’s status in the Name Manager if it is set to Local Scope or Workbook. If set to scope, then delete the range (be careful, this may break another formula on the sheet or may cause unintended behavior) and recreate it with Workbook status to resolve the #name issue or reference the range in another way (like using cell references).
7. #Name Error Due to a Missing Add-in of Microsoft Excel
If a particular formula requires an Excel add-in but your Excel does not have that add-in, then that may cause the #name error in Excel. Let us clear this by the following example by using EUROCONVERT:
8. #Name Error Due to Another Formula Showing the #Name Error
If there is a formula in your current formula range that is showing the #name error, then the current formula will also show the #name error. If your sheet is a large one and it is problematic to find all the #name formulas that your formula is dependent on, you may refer to the section Find the #Name Errors on a Large Sheet (discussed later in this article).
9. #Name Error Due to New Formula in Older Version of Excel
With each new version of Excel, Microsoft is constantly updating Excel with new functions and formulas. If a sheet created in a newer version (like Excel 2019) of Excel that has newer formulas (e.g., XLookUp) is opened in an older version of Excel (like Excel 2010), then those cells which use newer formulas may show the #name error. In this case, either the formulas should be replaced with older alternatives or the older version of Excel may be updated to the latest version, there is no other way to clear the #name error caused by this reason. Although, you can try some steps that can sometimes resolve the issue like re-formatting the numbers columns as numbers (if it converts to text in the older Excel version). Also, some Office 365 functions only work if a user is signed in with its Office 365 credentials, otherwise, it starts acting like Office 2019 and you may see a #name error on Office 365 functions (sometimes with an _xlfm prefixes) in an Excel sheet.
Try Assisted Troubleshooting
We are confident that for a majority of readers, the above-listed troubleshooting would be enough to clear the #name error but still there will be a few who may still encounter the #name error. These readers can benefit from the assisted troubleshooting features of Excel to resolve the #name issue, discussed under:
Use the Excel’s Formula Assistant
You can use this approach to clear the #name error in many cases.
Use the Excel’s Formula Wizard
If you are not comfortable with using formula assistant (as you are required to know some of the basic formula syntaxes), then using the formula wizard may do the trick for you.
Use the Name Manager
In a large workbook, it is not possible to remember the names of all the ranges and their scopes. Here, you can use the Name Manager to find the exact name of the range and its scope.
Find the #Name Errors on a Large Sheet
In the above examples, the examples used are simple ones but in a large sheet, it can become problematic to find the #name error. To do so:
Prevention is Better Than Cure
So, in the last, here are some tips for newbies to avoid the #name error in Excel. We have tried our best to cover all the aspects of the #name error and hopefully, may have succeeded in helping our precious readers. If you have any queries or suggestions, please let us know in the comments section.
Fix: “Formula Parse Error” with Examples on Google Sheets?DNS Record Types Explained (with Examples)How to Change the Name of a Sheet Within an Excel FileFix: Name Resolution for the Name Wpad Timed Out
