Excel functions such as AVERAGE, COUNTIF, and SUM are a useful way of shortcutting calculations, but that doesn’t mean that you won’t run into troubles. In fact, there are many types of errors that can pop up in Excel, and a common one is the #NAME? error. Here is why this error might occur and how you can fix it.
Learn how to recognize and resolve #NAME? errors in Excel.
About the #NAME Error in Excel
The #NAME error occurs in Excel when the program doesn’t recognize something in your formula. The most common cause is a simple misspelling of the function being used. For example, in the image below, the formula has VLOOKUP spelled incorrectly in the first instance (F5), so it produces the #NAME? error.
When this is corrected in the second instance (F6), it produces the correct result. Even better, there is no longer an error.
While this is the simplest and most common cause of the #NAME error, there are several others.
Incorrectly Entered Range Causing #NAME Error
If you have a formula in Excel with a range, and this isn’t stated correctly, the program will return a #NAME error. Consider the example below using the MIN and MAX functions. The MIN function is entered properly, but the MAX formula is not (F6).
When the formula in F6 is fixed from MAX(CC:C9) to MAX(C5:C9), it works correctly, and the error is gone.
Leaving out the colon in a range can also trigger the #NAME error.
Misspelling a Named Range Causes the #NAME Error
If you have a named range of cells, this can make for faster work with creating formulas in Excel. However, you will receive a #NAME error if you misspell that named range in your formula. Using the same MIN MAX example from before, the range was named “data” but was misspelled in the formula.
Once data is spelled correctly in the formula, the #NAME error disappears, and the correct answer is given.
Values in a Formula Without Quotes Causing the #NAME Error
When you have a text value in a formula, it should be enclosed in double quotation marks. If it is not, Excel will try to interpret that value as either a named range or a function name. When neither works, it will return a #NAME error.
In the following example, the LEN function is used to get the length of the word “Labrador.” In B5, the formula is provided without including the word in double quotation marks (“”). Because Labrador isn’t a named range or function, Excel returns a # NAME error.
When this is fixed by adding double quotes around “Labrador” in B5, the length of the word is given, and the #NAME error disappears.
Preventing #NAME Errors
The best way to prevent the #NAME error in Excel is to use the Formula Wizard. When you begin typing a function name in the formula bar, a series of choices will appear in the drop-down. Instead of continuing to type, you can avoid misspelling errors by selecting a function name from the list.
This will correct the most common cause of the #NAME error. The wizard even provides you with the standard syntax (range, criteria) for your function, which can also help avoid other causes of this error.
Use the Excel Formula Wizard to help avoid common causes of the #NAME error.
How to Find #NAME Errors
If you’re working with a large dataset, it may not be obvious where all of your errors lie. There are a few ways to find #NAME errors in Excel.
The first method is to press either Control-G (Go To) or F5 and choose Special. The Go To Special box will appear. Then, choose Formulas and select the box that says Errors. Click OK. You’ll then see every formula error in Excel.
The other way to find #NAME errors is by using the “Find” function. Under Editing, choose “Find,” or locate this by pressing Ctrl-F. In the Find what box, enter #NAME, and then choose either “Find Next” or “Find All.” You can then work through the errors.
Fixing #NAME? Errors in Excel
Fixing a #NAME error isn’t as simple as just deleting it and moving on. Each instance of this error requires careful inspection since several things can trigger it. The “Find” tool as described above is an excellent way to locate the errors, but then you will need to fix each one manually so that your formulas work as they were intended.
Other Issues with Excel #NAME Errors
There may be some advanced issues that cause the #NAME error to appear in Excel. For example, if you use a function that was introduced in a later version of Excel (such as IFNA), and then try to open that spreadsheet in an earlier version of Excel, you will probably get a #NAME error because the program doesn’t recognize the function.
Another problem can arise when saving an Excel sheet with another name (Save As). If you have a macro with formulas in the first file but don’t have macros enabled in the new file, you’ll probably get a #NAME error.
Still Need Some Help with Excel?
Were you able to find and resolve your Excel #NAME error? Whether this guide helped answer your questions or you still need some problem-specific assistance, Excelchat can help.
We have Excel experts standing by around the clock that are ready to deliver fast and friendly guidance on learning Excel or troubleshooting issues. Your first chat session is always free.
Leave a Comment