We will achieve the #NAME error in any event where Excel cannot recognize something in our formula or function. The most common occurrence is when we have a misspelled function but there are many other causes.
We fix the #NAME error mainly by correcting spelling or syntax. In this tutorial, we will explore some popular #NAME error situations and how to fix them.
Figure 1 – Name error in Excel
#NAME Error – Typo in the Entered Formula
In order to avoid Typos in formula names, it is important that we use the Formula Wizard in Excel. Once we enter a formula name, the formula Wizards helps direct the steps by matching the formula name, range and values. To illustrate, we will use the Formula Wizard to find the SUM of a list of times.
- We will go to the Formula Tab and click the Insert Function
Figure 2 – Fixing the #name error in excel
- We can either enter the description of the function, or select a category in the Insert Function window
Figure 3 – fixing the invalid name error in excel
- When we scroll and find the function, Excel would automatically load the Wizard for us where we can insert each argument for our calculations.
- Each time we enter the argument, Excel will give the appropriate information for each one.
Figure 4 – Using the Formula wizard to fix excel #name? error
#Name Error – Undefined Named Range
When there is a typo in the defined name or the defined name syntax is incorrect, then we will see the #NAME error.
Figure 5 – Excel #name error
A good way of eradicating this error is by going to the Formula Tab and select Define name. Next, we will enter the name of the Range and Cell reference in the New Name window.
Figure 6 – #name excel meaning
Again, we will go to Formulas and select Cost from the drop-down list of the Use in Formula button.
Figure 7 – #name error in excel
When we define the name of the range, the name error will be removed
Figure 8 – #Name error fixed
Misspelled Function Name
If we have incorrectly spelt the function name, we may also have the #NAME error. In this case, we incorrectly spelt the COUNTIF function.
Figure 9 – Excel #name error
If we correct the mistake, we will have this result
Figure 10 – Fixed #Name error
Omitted Quotation or Colon marks
In some cases, we may forget particular references with colons or insert the appropriate for range references, when such events occur, the formula will display the #NAME error. The solution is to go back and inspect the formula.
Figure 11 – Invalid name error in excel
When we have checked our formula, we will find the error. In this case, our cell reference was not entered correctly. If we correct it, we will have:
Figure 12 – Fixed #name error
Special Add-ins not enabled
Some functions and macros may require specific add-ins that has not been enabled by default in Excel. We should first find out if our function requires a special add-in. Next, we can enable the add-in by:
- Go to File and select Options
- Next, we will click Add-ins
- In the Manage List window, we will click Excel Add-ins and select Go
- Next, we will mark the relevant box and select OK.
Figure 13 – Using Excel Add-ins to fix name error.