If you’ve been working with Excel formulas, you’ve probably run into the frustrating #NAME? error. Excel throws this error to signal an issue with a formula, but it doesn’t tell you the exact problem.
The #NAME? error pops up when Excel can’t recognize a formula or its arguments. This usually indicates a typo or something missing with your formula, requiring correction.
There are several reasons why this error can occur. A common culprit is a simple misspelling of a function name, but there are other reasons, such as incorrect range names, misspelled cell ranges, missing quotation marks around text, missing colons in a cell range, or using formulas from a newer Excel version. Let’s look at some common causes and how to fix them.
Misspelled Formula or Function Names
One of the most frequent reasons for a #NAME error is misspelling the function’s name or using a function that doesn’t exist. If you’ve typed the function name incorrectly, you’ll see the #NAME? error.
For example, here, the function to count an item’s repeat is misspelled as “COUNTIIF” instead of “COUNTIF”, causing the #NAME? error.
Correcting the spelling of the function will resolve the issue.
To prevent this, use Excel’s formula suggestions instead of typing the entire formula. As you type, Excel displays a list of matching functions.
Double-click a suggestion or press TAB to select it. Then, fill in the arguments and press Enter.
Incorrect Cell Range
The #NAME error also occurs if the cell range is entered incorrectly. This might be because of a missing colon (
in the range, or an incorrect combination of letters and numbers.
In this example, the colon is missing (A1A6 instead of A1:A6), so it results in a #NAME error.
The error will also occur if the cell range uses the wrong combination of letters and numbers.
Here’s the corrected version with the proper cell range.
Misspelled Named Ranges
A named range gives a descriptive name to a cell or range of cells, instead of using the cell address. If you misspell a named range in your formula, you’ll get a #NAME error.
For instance, the range C4:C11 is named “Weight”. If you misspell “Weight” as “Wieght”, the SUM function returns a #NAME? error.
The error also occurs when you try to use a named range that hasn’t been defined. In this case, “Load” isn’t a named range.
Correctly spelling the named range resolves the error.
To avoid such errors, use the ‘Paste Name’ dialog to insert the named range into your formula. Press F3 while writing a formula to see a list of all named ranges. Then, pick the one you need.
This helps avoid typos and potential #NAME errors.
Check the Scope of Named Ranges
Another reason for a ‘#NAME?’ error is referencing a named range with a limited scope from a different worksheet. When creating named ranges, you can decide if its scope is for the whole workbook or just one sheet.
If the named range is limited to a specific sheet, you will see the #NAME error when you try to access it from another sheet.
To check a named range’s scope, go to ‘Name Manager’ from the ‘Formula’ tab, or press Ctrl + F3. You can see, edit, create, or delete named ranges here.
While you can check the named range’s scope in the Name Manager, you can’t modify it. Scope can only be set when a named range is created. Adjust the named range or define a new one to resolve this.
Text Without Double Quotes (" ")
Entering a text value without double quotes in a formula causes the #NAME error. Text values used in formulas, must always be inside double quotes (” “), even if it’s just a space.
For example, the formula below looks up ‘Pig’ in the table, but the text ‘Pig’ is without quotes, resulting in a #NAME? error.
Excel treats text within quotes as a text string. If a text value is not in double quotes, Excel thinks that it is a named range or formula. If Excel doesn’t find a matching name, it returns the #NAME error.
Adding double quotes around the text value will fix the error.
Note: Use straight double quotes (i.e. “Dog”). Smart quotes (i.e. ❝Dog❞) will cause a #NAME? error.
Using Newer Formulas in Older Excel Versions
New functions introduced in recent Excel versions won’t work in older versions. For instance, functions like CONCAT, TEXTJOIN, IFS, and SWITCH were introduced in Excel 2016 and 2019.
If you try using these functions in older Excel versions (like 2007, 2010, 2013), or open a file with such formulas in an older version, you’ll likely get a #NAME error. These versions don’t recognize the new functions.
Unfortunately, there isn’t a workaround for this. You can’t use newer formulas in older Excel versions. When working with older Excel versions, avoid using newer functions.
Also, saving a workbook containing macros as another file, but without enabling the macros in the newly saved file, will probably cause a #NAME? error.
Finding all #NAME? Errors in Excel
If you have a large spreadsheet with errors and you’re not sure where they are, you can use these methods to find #NAME errors.
Method 1: Using the Find and Replace Tool
If you want to locate all #NAME? errors in a sheet, the most efficient approach is to use the Find and Replace tool. Follow these steps:
- Select the cells in the worksheet you want to check for errors, or the entire worksheet (by pressing Ctrl + A).
- Click ‘Find & Select’ on the ‘Home’ tab and select ‘Find’ (or press Ctrl + F).
- In the Find and Replace dialog, type
#NAME?in the ‘Find what’ field and click ‘Options’.
- Choose ‘Values’ from the ‘Look in’ dropdown, and then click either ‘Find Next’ or ‘Find All’.
If you select ‘Find Next’, Excel will select the cells with the #NAME? error one at a time. If you select ‘Find All’, a list of cells with #NAME? error appears in the Find and Replace dialog box.
Method 2: Using the Go To Special Tool
The Go To Special feature is another way to find errors. This method locates all types of errors in a sheet, not just #NAME? errors. Here’s how:
- Open the sheet and click on ‘Find and Select’ in the Editing group of the ‘Home’ tab.
Alternatively, you can press F5 and click ‘Special’.
- In the Go To Special dialog box, choose ‘Formulas’, deselect all options except for ‘Errors’ under Formulas and click ‘OK’.
This will select all cells with any error type.
Avoiding #NAME? Errors in Excel
Using the Function Wizard is an efficient way to prevent #NAME? errors.
The Function Wizard allows you to create valid functions. It gives you a list of functions with their syntax, which you can easily implement. Here’s how to use it:
- Select the cell for your formula, then go to the ‘Formulas’ tab and select ‘Insert Function’ or click on the Function Wizard button ‘fx’ beside the formula bar.
You can also pick a function from the Function Library on the ‘Formulas’ tab.
2. In the ‘Insert Function’ dialog, choose a category from the dropdown menu. All functions in that category are listed in the ‘Select a function’ box. Select the function you want and click ‘OK’.
Alternatively, you can type a formula or partial name in the ‘Search for a function’ field, and double-click the function to select it.
- This will open the ‘Function Arguments’ dialog box. Here, you input the function’s arguments. For example, let’s find the quantity of ‘Pig’ using the VLOOKUP function.
Enter ‘Pig’ for Look_value. For Table_array, enter the table range (A1:D9), or click the upward arrow button to select the range. Enter ‘3’ for Col_index_num and ‘TRUE’ for Range_lookup. Then click ‘OK’.
The result will appear in the selected cell with the completed formula visible in the Formula bar.
Using the Function Wizard helps you avoid #NAME? errors.