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

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.

Table of Contents hide

Download Workbook

10 Examples of Reasons and Corrections of NAME Error in Excel

1. Incorrect Formula Name Causing NAME Error in Excel

2. Incorrect Range Using in a Formula

3. Misspelled Named Range Using in a Formula

4. Writing Text String in a Formula without Quotation

5. Using the Formula Assistance for Correcting NAME Error

6. Using the Formula Wizard for Correcting NAME Error

7. Using the Name Manager for Correcting NAME Error

8. Selecting the Range Instead of Typing

9. Using Go to Special Option for Finding NAME Error

Things to Remember

Practice Section

Conclusion

Download Workbook

Name Error.xlsx

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.

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

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.

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

➤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.

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

Result:

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

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

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.

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

➤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.

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

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

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

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.

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

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

Reasons and Corrections of NAME Error in Excel (10 Examples) (9)
➤ 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.

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

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

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

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.

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

➤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.

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

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

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

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.

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

➤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.

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

➤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.

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

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

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

Similar Readings

  • On Error Resume Next: Handling Error in Excel VBA
  • How to Remove Value Error in Excel (4 Quick Methods)
  • [Fixed] Excel Print Error Not Enough Memory
  • Excel VBA: Turn Off the “On Error Resume Next”
  • How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)

6. Using the Formula Wizard for Correcting NAME Error

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

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

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

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

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.

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

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

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

➤Press OK.

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

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

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

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.

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

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

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

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

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

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.

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

➤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.

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

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

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

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.

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

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.

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

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

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

➤Complete the other two arguments of this function.

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

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

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

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.

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

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

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

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

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

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

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

10. Using Find & Select Option for Finding NAME Error

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

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

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

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

➤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.

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

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

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

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.

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

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

  • VALUE Error in Excel: 7 Reasons with Solutions
  • REF Error in Excel (9 Suitable Examples)
  • How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)
  • [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel (8 Reasons)
  • How to Find Reference Errors in Excel (3 Easy Methods)
  • Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)
Top Articles
Latest Posts
Article information

Author: Stevie Stamm

Last Updated: 11/09/2022

Views: 5898

Rating: 5 / 5 (60 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Stevie Stamm

Birthday: 1996-06-22

Address: Apt. 419 4200 Sipes Estate, East Delmerview, WY 05617

Phone: +342332224300

Job: Future Advertising Analyst

Hobby: Leather crafting, Puzzles, Leather crafting, scrapbook, Urban exploration, Cabaret, Skateboarding

Introduction: My name is Stevie Stamm, I am a colorful, sparkling, splendid, vast, open, hilarious, tender person who loves writing and wants to share my knowledge and understanding with you.