In Excel, #NAME? error message means that “There is a spelling error in your Function Name or somewhere in your formula.”
But, that isn’t always the case.
You will also receive #NAME? Error when you haven’t quoted text strings, enabled required Add-In, and referenced correct ranges. Or, maybe you attempted to use unsupported functions and invalid Named Ranges.
Since the error mainly has to do with the formula typo or functions, you can troubleshoot this with the fixes mentioned below.
Also, to save yourself from this error message in the future, I have mentioned a few tips at the end of this article.
Check the Typo in Function Name
Have you spelled the Function Name correctly in the formula?
Sometimes, the slightest mistake like a formula typo can result in the #NAME? Error as Excel can not identify that function name.
In such a case, click on the error cell and look at the Formula bar.
If the spelling is wrong, change it and the error should be gone.
Double Quote Texts
Remember the rule for text strings in the formula is to always put them inside the double quotation mark (“”). When you don’t, you’ll get a #NAME? Error.
Make sure you have double-quoted all the words, spaces, or special characters in the formula.
For Instance, consider this formula,
=XLOOKUP(F4, B4:B12, C4:C12, No Sales, 0, 1)
Here, I haven’t quoted the No Sales text. So, the formula returned #NAME? Error instead of the specified word.
The accurate formula for the XLOOKUP to result in No Sales output would be:
=XLOOKUP(F4, B4:B12, C4:C12, “No Sales”, 0, 1)
Correct Range Reference
A colon (:) separates the cell ranges or array in Excel. For Instance, C3:C11 is an array.
But, if you forget to enter the colon in the cell reference, the formula fails to detect the range.
In this formula below, there’s no colon in between B3B11.
=INDEX(C3:C11, MATCH(F3, B3B11, 1))
Thus, it results in the #NAME? Error.
To fix the error, add a colon to make it an appropriate range reference.
Check Named Range
Named Ranges are easy to enter and use in the formulas. But, are you sure whether the Named Range you are using exists?
Let’s assume, I have entered the formula:
=HLOOKUP("French", Score, 4, 1)
Here, Score is the table_array. But, since there’s no named range with “Score”, Excel sent #NAME? Error.
Or, in another instance, it is possible that you may have entered the Named Range with incorrect spelling. Pay attention to the singular or plural words.
For Instance, even the extra (s) in the Score typed as (Scores) becomes invalid as Excel cannot recognize it.
To resolve this, head to the Formulas tab and click on Name Manager. Check the Spelling.
If you can’t find the Named Range, learn how to create it from this article.
Load Add-In
If you’re using the functions that require Add-in like EUROCONVERT, ensure you’ve enabled it in your Excel.
You must load Euro Currency Tools Add-In for that function first. Else, you will receive #NAME? Error.
Just like that, you need to enable the Analysis ToolPak Add-In to use the functions that require it.
- From File, click Options.
- Choose Add–ins. On Manage, pick Excel Add-ins and Go.
- Tick the option for Add-In. Hit OK.
Now, once you have turned on Add-In, try using the formula.
Upgrade Excel to the new Version
Excel’s few functions are only available to new versions like 2021 and later. So, when you attempt to use functions like SEQUENCE in the older version, you’ll get the error.
You can see the supported version of the specific function from the official page. Then, compare it with your current version.
To check, from File, choose Account and hit About Excel.
See the version on top. If needed, upgrade to the new Office version to use all new functions.
Tips to Avoid #NAME? Error in Excel
Above we discussed how to solve the #NAME? Error. But, if you just follow these given tips, you could easily avoid this error.
- For formula entry, use the Formula assistance. This ensures you aren’t typing the wrong spelling for function.
For Instance, for=FORECAST.ETS.SEASONALITY()formula, enter=FO, and click on the Name from the lists.
- Opt for Formula Wizard to input the formula arguments, especially if you’re a beginner. This way you won’t miss the arguments.
- For lengthy formulas, referencing arrays can be complex. Instead, insert Named Ranges.