Explanation
The #NAME? error occurs when Excel can’t recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a matter of correcting spelling or a syntax problem. The examples below show misconfigured formulas that return the #NAME error and the steps needed to fix the error and get a working formula again.
Function name misspelled
In the example below, the VLOOKUP function is used to retrieve an item price in F3. The function name “VLOOKUP” is spelled incorrectly, and the formula returns #NAME?
=VLOKUP(E3,B3:C7,2,0) // returns #NAME?
When the formula is fixed, the formula works properly:
=VLOOKUP(E3,B3:C7,2,0) // returns 4.25
Range entered incorrectly
In the example below, the MAX and MIN functions are used to find minimum and maximum temperatures. the formulas in F2 and F3, respectively, are:
=MAX(C3:C7) // returns 74
=MIN(CC:C7) // returns #NAME?
Below the range used in F3 has been fixed:
Note: forgetting to include a colon (:) in a range will also trigger the #NAME? error.
Source data contains #NAME!
If the source data for a function contains a #NAME? error, the calling function might return a #NAME? error. For example, in the worksheet below, the range C3:C7 contains a #NAME? error, so the SUM function also returns #NAME?
=SUM(C3:C7) // returns #NAME?
To fix this problem, resolve the errors in the source data.
Named range misspelled
In the example below, the named range “data” equals C3:C7. In F2, “data” is misspelled “daata” and the MAX function returns #NAME?
=MAX(daata) // returns #NAME? error
Below, the spelling is corrected and the MAX function correctly returns 325 as the maximum sales number:
=MAX(data) // returns 325
Notice named ranges are not enclosed by quotes ("") in a formula.
Named range has a local scope
Text value entered without quotes
When a text value is input without double quotes, Excel thinks tries to interpret the value as a function name, or named range. This can cause a #NAME? error when no match is found. In the example below, the LEN function is used to get the length of the word “apple”. In B3 the formula is entered without the text string “apple” in quotes (""). Because apple is not a function name or named range, the result is #NAME?
=LEN(apple) // returns #NAME?
Below, quotes have been added and the LEN function now works correctly:
=LEN("apple") // returns 5
Text value with smart quotes
Text values needed to be quoted with straight double quotes (i.e. “apple”). If “smart” (sometimes called “curly”) quotes are used, Excel won’t interpret these as quotes at all and will instead return #NAME?
=LEN(“apple”) // returns #NAME?
To fix this problem, simply replace the smart quotes with straight quotes:
=LEN("apple") // returns 5
Note: some applications, like Microsoft Word, may change straight quotes to smart quotes automatically, so take care if you are moving a formula in and out of different applications or environments.
Explanation
The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the “range intersect” operator and the #NULL! error is reporting that the two ranges do not intersect. This is baffling to the average user but in most cases replacing the space with a comma or colon as needed will fix the problem.
Example 1 - space instead of colon
In the screen below, the formula in C9 returns the #NULL error:
=SUM(C3 C7) // returns #NULL!
In this case, the input was meant to be the range C3:C7, but the colon did not get typed. Once the colon is added, the error is fixed:
=SUM(C3:C7) // returns 1205
Example 2 - space instead of comma
In the example below, the formula in C5 returns the #NULL error:
=SUM(C2,F2 I2) // returns #NULL!
Here, a space was typed instead of a comma between F2 and I2. Once the colon is added, the error is fixed:
=SUM(C2,F2,I2) // returns 1205
