Buzz
In previous articles, Mytour has guided you on fixing Circular Reference errors in Excel. Here, Mytour will further guide you on some methods to resolve the #NAME? error in Excel.
1. Understanding the #NAME? Error in Excel
The #NAME? error occurs when Excel fails to recognize something. If the #NAME? error happens frequently in Excel, it might be due to misspelled function names or other reasons.
Essentially, to fix the #NAME? error in Excel, you just need to correct the spelling or syntax error.
In the example below, the formula is incorrectly written, resulting in the return of an error #NAME:
2. Fixing the #NAME? Error in Excel
As mentioned earlier, there are various reasons for the #NAME? error in Excel. Here, Mytour will guide you through some ways to fix this error.
2.1 Misspelled Function Names
In the example below, the VLOOKUP function is used to retrieve item prices in cell F3. However, the function name VLOOKUP is misspelled, so the formula will return the #NAME? error.
=VLOKUP(E3,B3:C7,2,0) // returns the #NAME? error
After the formula is corrected, it will return the accurate value:
=VLOOKUP(E3,B3:C7,2,0) // returns the value 4.25
2.2 Incorrect Range Input
In the example below, the MAX and MIN functions are used to calculate the highest and lowest temperatures. The formulas in cells F2 and F3 respectively are:
=MAX(C3:C7) // returns the value 74
=MIN(CC:C7) // returns the #NAME? error
After the range in cell F3 is corrected, it will return the accurate value:
Note: If you forget to include the colon (:) in the range, it will also return the #NAME? error.
2.3 Misspelled Range Name
In the example below, the range 'data' is equivalent to C3:C7. In cell F2, 'data' is misspelled as 'daata', causing the MAX function to return the #NAME? error.
=MAX(daata) // returns the #NAME? error
After the spelling error is corrected, the MAX function will return the value 325:
=MAX(data) // returns the value 325
Note: Range names should not be enclosed in double quotes ('') in the formula.
2.4 Text Value Not Enclosed in Double Quotes
If a text value is not enclosed in double quotes, Excel will try to interpret the value as a function name or range name. This can result in the #NAME? error if Excel cannot find a matching result.
In the example below, the LEN function is used to get the length of the word 'apple'. In cell B3, the formula is entered without enclosing the text string in double quotes ('). Since apple is not a function name or range name, the result returned is the #NAME? error.
=LEN(apple) // returns the #NAME? error
After enclosing the text string in double quotes, the LEN function will return the accurate length of the string 'apple':
=LEN('apple') // returns the value 5
Here is a guide on fixing the #NAME? error in Excel. Mytour has provided some methods for you to correct the #NAME? error. If you have any questions or need assistance with Excel files, feel free to leave your comments below the article.
