Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

The #NAME Excel Error – How to Find and Fix #NAME Errors in Excel

Excel functions such as AVERAGE, COUNTIF, and SUM are a useful way of shortcutting calculations, but that doesn’t mean that you won’t run into troubles. In fact, there are many types of errors that can pop up in Excel, and a common one is the #NAME? error. Here is why this error might occur and how you can fix it. 

Name spelled out with wooden blocks.

Learn how to recognize and resolve #NAME? errors in Excel.

About the #NAME Error in Excel

The #NAME error occurs in Excel when the program doesn’t recognize something in your formula. The most common cause is a simple misspelling of the function being used. For example, in the image below, the formula has VLOOKUP spelled incorrectly in the first instance (F5), so it produces the #NAME? error. 

Screen shot of a V-lokup.

When this is corrected in the second instance (F6), it produces the correct result. Even better, there is no longer an error. 

Vlookup screen shot.

While this is the simplest and most common cause of the #NAME error, there are several others. 

Incorrectly Entered Range Causing #NAME Error

If you have a formula in Excel with a range, and this isn’t stated correctly, the program will return a #NAME error. Consider the example below using the MIN and MAX functions. The MIN function is entered properly, but the MAX formula is not (F6). 

Max function in excel screen shot.

When the formula in F6 is fixed from MAX(CC:C9) to MAX(C5:C9), it works correctly, and the error is gone. 

Max screen shot.

Leaving out the colon in a range can also trigger the #NAME error. 

Misspelling a Named Range Causes the #NAME Error

If you have a named range of cells, this can make for faster work with creating formulas in Excel. However, you will receive a #NAME error if you misspell that named range in your formula. Using the same MIN MAX example from before, the range was named “data” but was misspelled in the formula. 

Max data screen shot.

Once data is spelled correctly in the formula, the #NAME error disappears, and the correct answer is given. 

Max screen shot.

Values in a Formula Without Quotes Causing the #NAME Error

When you have a text value in a formula, it should be enclosed in double quotation marks. If it is not, Excel will try to interpret that value as either a named range or a function name. When neither works, it will return a #NAME error. 

In the following example, the LEN function is used to get the length of the word “Labrador.” In B5, the formula is provided without including the word in double quotation marks (“”). Because Labrador isn’t a named range or function, Excel returns a # NAME error. 

Len screen shot.

When this is fixed by adding double quotes around “Labrador” in B5, the length of the word is given, and the #NAME error disappears. 

Len screen shot.

Preventing #NAME Errors

The best way to prevent the #NAME error in Excel is to use the Formula Wizard. When you begin typing a function name in the formula bar, a series of choices will appear in the drop-down. Instead of continuing to type, you can avoid misspelling errors by selecting a function name from the list. 

This will correct the most common cause of the #NAME error. The wizard even provides you with the standard syntax (range, criteria) for your function, which can also help avoid other causes of this error. 

Function arguments screen shot.

Use the Excel Formula Wizard to help avoid common causes of the #NAME error.

How to Find #NAME Errors

If you’re working with a large dataset, it may not be obvious where all of your errors lie. There are a few ways to find #NAME errors in Excel. 

The first method is to press either Control-G (Go To) or F5 and choose Special. The Go To Special box will appear. Then, choose Formulas and select the box that says Errors. Click OK. You’ll then see every formula error in Excel. 

Go to Special box in excel.

The other way to find #NAME errors is by using the “Find” function. Under Editing, choose “Find,” or locate this by pressing Ctrl-F. In the Find what box, enter #NAME, and then choose either “Find Next” or “Find All.” You can then work through the errors. 

Fixing #NAME? Errors in Excel

Fixing a #NAME error isn’t as simple as just deleting it and moving on. Each instance of this error requires careful inspection since several things can trigger it. The “Find” tool as described above is an excellent way to locate the errors, but then you will need to fix each one manually so that your formulas work as they were intended. 

Other Issues with Excel #NAME Errors

There may be some advanced issues that cause the #NAME error to appear in Excel. For example, if you use a function that was introduced in a later version of Excel (such as IFNA), and then try to open that spreadsheet in an earlier version of Excel, you will probably get a #NAME error because the program doesn’t recognize the function. 

Another problem can arise when saving an Excel sheet with another name (Save As). If you have a macro with formulas in the first file but don’t have macros enabled in the new file, you’ll probably get a #NAME error. 

Still Need Some Help with Excel?

Were you able to find and resolve your Excel #NAME error? Whether this guide helped answer your questions or you still need some problem-specific assistance, Excelchat can help. 

We have Excel experts standing by around the clock that are ready to deliver fast and friendly guidance on learning Excel or troubleshooting issues. Your first chat session is always free. 

 

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc