Reasons and Corrections of NAME Error in Excel (10 Examples)

If you are looking for the reasons and corrections of NAME Error in Excel, then you are in the right place.

Sometimes working with a dataset in Excel you may unwittingly make some mistakes while using formulas and then it shows NAME Error instead of your results. To know the causes of this error and to avoid it you can follow this article.

Download Workbook


10 Examples of Reasons and Corrections of NAME Error in Excel

I will use the following table, which contains the Sales Records of a company, for demonstrating the causes and the ways of rectifying NAME Error in Excel easily.

For this purpose, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

name error in excel


1. Incorrect Formula Name Causing NAME Error in Excel

Let’s say, you want to get the sum of Sales for Apple using the SUMIF function. But you can get here NAME Error due to typing a wrong function name like this example.

name error in excel

➤I have written the following formula for getting the Sum of Sales for Apple.

=SUMF(B5:B12,"Apple",D5:D12)

But, here I have typed SUMF instead of SUMIF function.

Incorrect Formula Name

Result:

For using the incorrect function name, you will get the NAME Error like the following result.

Incorrect Formula Name

Read More: [Fixed] Excel Found a Problem with One or More Formula References in This Worksheet


2. Incorrect Range Using in a Formula

Here, we want to get the Region for the Item Banana by using the VLOOKUP function.
But for using the wrong range in the formula we can get the NAME Error instead of our desired result.

name error in excel

➤I am using the following formula in cell F6 for getting the Region.

=VLOOKUP(R17,B5C12,2,FALSE)

But, here I have typed the range as B5C12 instead of B5:C12.
Due to the missing “:” sign in the range we will not get the proper result here.

Incorrect Range

Result:
For using the incorrect range, you will get the NAME Error like the following result.

Incorrect Range

If you use any range exceeding the limit of the range A1: XFD1048576 like A1: XFD1048580 then you will also get the NAME Error.

Read More: Errors in Excel and Their Meaning (15 Different Errors)


3. Misspelled Named Range Using in a Formula

Because of not using the named range correctly in a formula can occur the NAME Error also.

name error in excel

➤ Here, the range of the cells in the column Item is named as fruits.

misspelled named range
➤ The following formula has been used for getting the Sum of Sales for Apple.

=SUMIF(fruit,"Apple",D5:D12)

I have used here fruit as the named range instead of fruits.

misspelled named range

 

Result:
For using the wrong named range in the formula, you will get the NAME Error like the following result.

name error in excel


4. Writing Text String in a Formula without Quotation

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.

name error in excel

➤I have used the following formula for getting the Region

=VLOOKUP(Banana,B4:D12,2,FALSE)

But, here I have typed Banana instead of “Banana” for the lookup value in this formula.

text string without quotation

Result:
For missing the quotation marks for the text string Banana in the formula, you will get the NAME Error like the following result.

text string without quotation


5. Using the Formula Assistance for Correcting NAME Error 

To get rid of the NAME Error, you can use Formula Assistance for having the right formula.

name error in excel

➤When you start to write your function name after pressing the equal sign, you will see all of the matched function names according to your writing.
➤Then just click on your desired formula and press the TAB key.

formula assistance

➤After that, you have to complete the formula by using the correct arguments of this function.

=SUMIF(B5:B12,"Apple",D5:D12)

Here, for the SUMIF function, the first argument was the criteria range which is the range B5:B12, the second argument was the criteria which is Apple and the third argument was the sum range which is the range D5:D12.

Finally, close the bracket.

formula assistance

Result:
After entering the correct formula you will get your desired value for the Sum of Sales for Apple.

name error in excel


Similar Readings


6. Using the Formula Wizard for Correcting NAME Error 

You can use the Formula Wizard for avoiding the NAME Error like this example.

name error in excel

➤Select the output cell where you want your result and then the marked sign.

formula wizard

Then, the Insert Function Dialog Box will pop up.
➤Select any of the options in the box select a category ( I have used the Most Recently Used Option)
➤Choose your desired function in the select a function box ( I am using the SUMIF function here)
➤Press OK.

name error in excel

After that, the Function Arguments Wizard will open up.
➤Select the following

Range → B5:B12
Criteria →  “Apple”
Sum_range →  D5:D12

➤Press OK.

formula wizard

Result:
After that, you will get your desired value for the Sum of Sales for Apple.

name error in excel


7. Using the Name Manager for Correcting NAME Error 

While you have so many named ranges for your dataset it is normal to forget the correct named range for using in a formula. So, to check out the correct named range you can use the Name Manager Option.

name error in excel

➤ Here, I have used items as the name for the range of cells in the Item column.

using name manager

➤Go to Formulas Tab>>Defined Names Group>>Name Manager Option

using name manager

After that, the Name Manager Wizard will appear.
➤For the items named range, you can see all of the information for this range like the Values, Sheet Name, etc. By getting the information you can enter the correct name of the range in the formula.

name error in excel

➤We have used the following formula using the correct named range.

=SUMIF(items,"Apple",D5:D12)

Here, items is our correct name of the range.

using name manager

Result:
Afterward, you will get your desired value for the Sum of Sales for Apple.

name error in excel


8. Selecting the Range Instead of Typing

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.

name error in excel

For the table range argument of the VLOOKUP function, we want to select the whole data range from B5 to D12.
➤Select the first cell B5 of the range
➤Drag down and right the arrow sign.

selecting the range instead of typing

In this way, you will be able to select the whole range.

selecting the range instead of typing

➤Complete the other two arguments of this function.

selecting the range instead of typing

Result:
After pressing ENTER, you will get the corresponding Region for the item Banana.

name error in excel


9. Using Go to Special Option for Finding NAME Error

For a large dataset it is difficult to find all of the NAME Errors, so you can use the Go To Special Option to find all of the errors at once.

name error in excel

➤Go to Home Tab>>Editing Group>> Find & Select Dropdown>> Go to Special Option.

Go To Special

Then the Go To Special Dialog Box will appear.
➤Select Formulas
➤Click on Errors
➤Press OK.

name error in excel

Result:
In this way, you will be able to select the cell where this error has occurred.

Go To Special


10. Using Find & Select Option for Finding NAME Error

You can use the Find & Select Option for identifying the NAME Errors.

name error in excel

➤Go to Home Tab>>Editing Group>> Find & Select Dropdown>> Find Option.

Find & Select

➤Type the error name #NAME? in the Find what box.
➤Select the following

Within→ Sheet
Search →  By Rows
Look in →  Values

➤Click on Find All.

Find & Select

Result:
After that, you will get the cell name $F$7 which has the NAME Error.

name error in excel


Things to Remember

⦿ For opening up an Excel Workbook which has used the new version (like Microsoft Excel 365 version or Microsoft Excel 2019) functions such as the FILTER function, the XLOOKUP function, etc. in an older version may cause the NAME Error.


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, I tried to cover the reasons and corrections of NAME Error in Excel. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.


Related Articles

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

ExcelDemy
Logo