[Fixed!] NAME Error in Excel (7 Reasons with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Excel is a powerful tool widely used for data analysis, organization, and reporting. While working with formulas and functions, you may come across a common hurdle known as name error in Excel.
Understanding the causes of name error, their implications, and how to resolve them is crucial for ensuring accurate calculations and maximizing efficiency.
In this guide, we will explore the world of name error in Excel and provide easy-to-implement solutions to overcome them effectively. Let’s dive into name error in Excel together.

name error in excel


What Is #NAME Error in Excel

The #NAME error occurs in Microsoft Excel when a formula includes a function or range name that is unrecognized or misspelled. Excel cannot interpret a text string within the formula as a valid component, resulting in this name error message.
If, for instance, you mistakenly provide an incorrect cell reference, Excel will be unable to recognize it and will generate a #NAME error. The cell containing the formula will display the error message, hindering the formula from producing a valid outcome.

name error happening in excel


[Fixed!] NAME Error in Excel: 7 Valid Reasons with Solutions

Let’s assume we have a sheet of the Academic Performance of Students of a specific institution. This dataset includes Name and their marks for different subjects like S. Science, Math, G. Science, and Language under columns B, C, D, E, and F respectively.

Dataset of academic performance of students

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

Here, we will find out the marks of a certain subject of a specific student in cell D14 by using a formula in Excel. To do this, we’ll use the VLOOKUP and the MATCH functions.

Finding mark of specific student in a certain subject in Excel

Now, we’ll utilize this dataset to show reasons for #NAME errors in Excel using multiple examples. We’ll show the corresponding solutions to them too. So, let’s explore them one by one.
Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


Reason 1: Using Incorrect Function Name Will Cause #NAME Error

Let’s say, you want to get the Marks for Cilan Lee in Language using the VLOOKUP function. But you got here the #NAME error due to typing a wrong function name like this example. Here, we typed it as VLOOKOP instead of VLOOKUP.

The formula we put in cell D14 is the following:

=VLOOKOP(D12,B5:F10,MATCH(D13,B4:F4,0),FALSE)

incorrect function name resulting name error in Excel


Solution: Utilize Formula AutoComplete Feature

To get rid of the error, you can use Formula AutoComplete to have the right function name and structure in the formula.

  • At first, write the initial one or two letters of the function name in the Formula Bar and Excel will suggest various functions. From the list, select your preferred function and press the TAB key to bring the function into operation.

selecting VLOOKUP function from the suggestion

  • Now, you can see the arguments of the function while giving inputs into the formula. It’ll help to give correct inputs. After completing the formula, press the ENTER key.

using formula autocomplete feature

Behold, Excel eliminated the NAME error, and the cell (D14) now displays the correct output.

not returning name error after using right spelling of function name

In this process, you don’t have to write the full function name. As a result, the percentage of errors gets reduced.


Reason 2: Utilizing Incorrect Range or Typo in Formula

Here, we want to get output like Example 1. But, it’s showing the #NAME error in the output cell (D14). We employed the following formula to accomplish the task.

=VLOOKUP(D12,BC:F10,MATCH(D13,B4:F4,0),FALSE)

Look, we gave BC:F10 in the table_array argument of the VLOOKUP function. That’s the reason behind showing errors in Excel.

error in range in formula in Excel


Solution 1: Select Range Instead of Typing in Formula

While typing the range in a formula you may sometimes miss the “:” sign or you may refer to a wrong range which causes the #NAME error. To avoid this you can select the range instead of typing.

For the table_array argument of the VLOOKUP function, we want to select the whole data range from B5 to F10.

  • Select the first cell B5 of the range. Then, drag the arrow sign in the down and right direction up to cell F10. Next, release the mouse click.

selecting range instead of writing it manually

In this way, you will be able to select the range without any error and can get the correct output.

correct range in argument of formula


Solution 2: Use Insert Function Command

You can also use the Insert Function command to avoid errors like this.

  • Select cell D14 and click on the Insert Function icon beside the Formula Bar.

clicking on Insert Function icon

It’ll open the Insert Function dialog box. In the Search for a function box, you can search for any function.

  • Select the VLOOKUP function from the recently used functions and click OK. Otherwise, just double-click on the function to select it.

working on Insert Function dialog box

Instantly, the Function Arguments wizard pops up.

  • In the respective boxes, give the arguments of the VLOOKUP function as we wrote in the formula. Here, you can select the range from the worksheet and it’ll be input as an argument automatically.
  • Later, click OK.

giving function arguments in the Function Arguments dialog box

Witness the removal of the #NAME error, allowing the cell to exhibit the accurate output.

returning correct output in cell


Reason 3: Causing #NAME Error If Named Range Is Not Defined

In the following formula, we used a named range (Marks) as the table_array argument of the VLOOKUP function.

=VLOOKUP(D12,Marks,MATCH(D13,B4:F4,0),FALSE)

But the formula returns the #NAME error in the output cell.

returning name error for undefined named range

But, if we go back to the Dataset worksheet, and select the B4:F10 range, we can see the Marks named range in the Name Box. So, it means this named range is available in this workbook, so why doesn’t it work with the formula? Let’s search for the answer.

Marks named range in Dataset worksheet


Solution: Make Use of Name Manager

Name Manager in Excel is a feature that allows you to view, create, edit, and delete named ranges and defined names in your workbook. It provides a central location to manage all the named ranges and names used in your formulas. To access it,

  • Go to the Formulas tab >> Name Manager on the Defined Names group.

navigating in the Formulas tab in Excel

  • In the Name Manager wizard, if you click on the Marks name, you can see the range of this name in the Refers to box. Actually, this named range is applicable to the Dataset worksheet only. That’s why it didn’t work in the formula of another sheet.
  • Now, click on the New button at the top of this dialog box.

Name manager dialog box

  • In the New Name dialog box, fill up the following fields like the image below, and click OK.

creating new name in the active worksheet

Note: Here, we set the Scope for the current worksheet.

Notice the absence of the #NAME error, resulting in the cell (D14) showing the correct output.

giving right output for using defined named range


Reason 4: Using Misspelled Named Range in Formula

While you have so many named ranges for your dataset it is normal to forget the correct named range for using in a formula. We utilized the subsequent formula to carry out this task.

=VLOOKUP(D12,Mark,MATCH(D13,B4:F4,0),FALSE)

Here, Mark is a named range. But the formula isn’t working yet.

name error for spelling mistake in named range in Excel


Solution: Select Range and Check the Name Box

You can use the Name Manager to solve this problem. Already, we showed it in the previous example.
Another way to resolve the issue is to select the range where we defined the range. Then, get a look into the Name Box at the left of the Formula bar. Here, you can find the right spelling of your named range.
In this case, it is Marks, but we wrote it as Mark in the formula. That’s why it returned an error.

watching correct spelling of named range in the Name Box in Excel

Now correct the range name in the formula and it looks like the following.

=VLOOKUP(D12,Marks,MATCH(D13,B4:F4,0),FALSE)

Observe how the #NAME error has vanished, leading to the cell presenting the correct result.

not returning name error now


Reason 5: Writing Text String Without Quotation in Formula

If you write any text string in a formula without using a quotation sign for this string, then you will get the #NAME error instead of the correct result. Here, we used the text string Cilan Lee without any quotation in the following formula.

=VLOOKUP(Cilan Lee,B5:F10,MATCH(D13,B4:F4,0),FALSE)

text string without quotation sign resulting in error


Solution: Use Double-Quote Around Text String

Just insert a pair of double quotes around the text string and the formula be like this:

=VLOOKUP(“Cilan Lee”,B5:F10,MATCH(D13,B4:F4,0),FALSE)

Take note that Excel rectified the #NAME error, and the cell is now exhibiting the accurate output.

double quote sign is given around the text string in the formula


Reason 6: Returning #NAME Error for Compatibility Issue

The #NAME error occurs when there is a compatibility issue in Excel, leading to the inability to recognize a specific function or reference. This error is commonly encountered when using functions or features that are not supported in the current version of Excel.

For instance, dynamic array functions like UNIQUE, FILTER, SORT, and others, which were introduced in Excel 365, are unavailable in older versions. If any of these functions are attempted in Excel 2019 or earlier, a #NAME error will be generated.

Solution:

To address this issue, ensure that you are using a compatible version of Excel that supports the function or feature you are trying to use. Additionally, consider updating your Excel version or utilizing alternative methods or functions that are compatible with your current version.

By resolving the compatibility issue, you can eliminate the #NAME error and achieve the desired functionality in Excel.


Reason 7: Using Specific Functions Without Enabling Add-ins

If you come across the #NAME error, it could indicate that a specific formula requires an Add-in that is either not installed or enabled in your Excel.

For instance, when using the EUROCONVERT function, it necessitates enabling the Euro Currency Tools add-in. You can find it in the Excel Add-ins.

  • In the Developer tab, click on Excel Add-ins in the Add-ins group.

navigating in the developer tab

  • In the Add-ins dialog box, select the necessary available add-in and click OK.

Excel add-ins dialog box

Moreover, Excel will return the same #NAME error, if you attempt to use any custom function which is not associated with the respective sheet.


How to Find #NAME Error in Excel

For a large dataset, it is difficult to find all of the #NAME errors properly. In this instance, you can see the following two methods.


1. Use Find and Replace Dialog Box

  • First, go to Home >> Find & Select >> Find.

clicking on find option

  • In the Find and Replace dialog box, write the text string you want to find. Here, we want to locate the #NAME? error, so write this in the box.
  • Then, select the following,

WithinSheet
SearchBy Rows
Look inValues

  • Next, click on Find All.

working on Find and replace dialog box to find cells with name error

After that, you will get the cell address $D$14 which has the #NAME? error.

showing cell address at bottom


2. Use the Go To Special Option to Find #NAME Error

To use this option,

  • Initially, navigate to Home >> Find & Select >> Go To Special.

using Go To Special command

  • In the Go To Special dialog box, check the radio button of Formulas and deselect all options except Errors inside it. Then, click OK.

selecting errors inside Formulas option

Excel instantly selects the cell with the #NAME error. It can also select multiple cells with the same criteria.

cell with name error selected in Excel


Things to Remember

  • Double-check the spelling and syntax of your formula. Even a small typo can result in a #NAME error.
  • Keeping your Excel version up to date ensures that you have access to the latest functions and features.
  • Implement proper error-handling techniques in your formulas to handle potential errors like #NAME. Use functions like IFERROR or ISERROR to handle errors gracefully and provide alternative outputs or error messages.
  • If you are unable to resolve the #NAME? error using the above tips, consider seeking help from the Excel community. Excel Forum is a great platform in this case.

Frequently Asked Questions

1. What does the Name Error message look like in Excel?

The name error message is usually displayed as #NAME? in the cell where the error occurs. It indicates that Excel cannot recognize the name used in the formula or function.

2. Are there any built-in functions in Excel to help with Name Errors?

Yes, Excel provides some functions that can help handle name errors, such as ISERROR, ISNA, and IFERROR. These functions allow you to test for errors and provide alternative outputs or error-handling mechanisms in your formulas.

3. Can macros or VBA code contribute to Name Errors in Excel?

Yes, macros or VBA codes can potentially introduce name errors if they contain references to invalid or non-existent names.

4. Can circular references cause Name Errors?

Circular references can cause different types of errors in Excel, but they typically do not result in name errors. Circular references are more likely to cause Circular Reference or Ref# errors.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly and will help you practice yourself.


Conclusion

In conclusion, name errors in Excel can be a common and frustrating issue that users may encounter while working with spreadsheets. These errors occur when a named range, function, or formula in Excel refers to a name that does not exist or is incorrectly defined.
Excel provides helpful error-checking features and formula auditing tools that can assist in identifying and resolving name errors. In this reference, we tried to demonstrate 7 different and valid reasons for this error. Also, we showed the corresponding solutions. Leveraging these, you can significantly simplify the troubleshooting process and enhance overall productivity.
Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo