How to Fix the #NAME Error in Excel

Whether you are a beginner or an experienced Excel user, you might have encountered the #NAME error. This guide will discuss the probable reasons and effective ways to eliminate it quickly.

What is the #NAME error in Excel?

The #NAME error in Microsoft Excel is a common error message that appears when a formula contains a text that is not recognized as a valid function, named range, or formula.

How do I fix the #NAME error in Excel?

1. Check the function name spelling

The most common reason for the #NAME error in Excel is the wrong spelling of the function’s name. When you mistype the function’s name, Excel can’t recognize it and displays the #NAME error.

For example, if you are applying the COUNTIF formula on your sheet, but while applying the formula, you mistyped the spelling of COUNTIIF, Excel won’t consider it a function; you will get the #NAME error instead of the result.

To avoid the typos in formula names, use the Formula Wizard. Whenever you type a formula name in a cell or Formula Bar, you will get a list of formulas matching your typed words. Select from the drop-down list; once you have the formula name and opening parentheses, you will see the syntax as hover text.

Alternatively, you can select the cell with the formula and click the Insert function next to the Formula Bar; you will see the wizard with the information to help you correct the formula.

2. Invalid named range

Microsoft Excel allows you to use named ranges, which makes differentiating between cells more accessible, and you can easily refer to the data in it.

However, if you use a formula that refers to a named range that has been deleted, misspelled, or doesn’t exist, you receive the #NAME error.

To avoid this, ensure you have selected the named range of the correct scope (within the worksheet), and the named range is correctly spelled in the formula; for that, follow these steps:

1. Click the Formulas tab, then under Defined Names, select Define Name.
2. Choose the Defined Name, and for Scope, select Workbook (refer to the entire workbook) or specific sheet.
3. Click OK.
4. Take your cursor in the formula syntax to the point where you want the name you created to appear.
5. Next, go to the Formulas tab, locate Defined Names, click Use in Formula, and choose the defined name from the drop-down menu.

3. Double Quote missing from the text value

If you are adding text references in formulas, you must enclose the text in double quotation marks, even when you are using just a space.

To fix it, closely examine the syntax and check the text and space to ensure all of them are enclosed in double quotes.

4. Using the new function in the older Excel version

If you want to use a new function in an older version of Microsoft Excel, you will get the #NAME error. For example, some dynamic array functions like FILTER & UNIQUE are introduced with Microsoft 365; therefore, you can’t use them with earlier versions like 2019, 2016, 2013, 2007, etc.

5. Wrong range reference

Inserting the wrong range in a formula can cause the #NAME error to appear. This usually happens if you type in the wrong cell reference or call a cell outside the selected Excel range. To avoid this issue, use the mouse to drag and select the range you need for the formula.

6. Custom function is not available

Some Excel functions need you to install add-ins on your desktop app for them to work correctly, like the Euroconvert function. This function needs you to enable the Euro Currency Tools add-in.

So before using any custom function, make sure you enable the Add-in related to it; for that, follow these steps:

1. Go to the File tab.
2. Click Options to open the Excel Options window.
3. Choose Add-ins from the left pane.
4. Locate the Manage list box, choose Excel Add-ins, then click Go.
5. Select the add-in and click OK to confirm.

This will help you use the custom functions; however, you must disable the ones you don’t use to prevent issues like Excel has run into an error and file not being found; read this to learn more about it.

7. Check for a missing colon

When you mention a range reference, you must add a colon in between; if that is missing, you will get a #NAME error. To avoid this, ensure whenever you select a range reference, you separate them using a colon in between.

How can I find #NAME? errors in Excel?

1. Select the entire range of cells you want to inspect.
2. Go to the Home tab, locate the Editing group, then click Find & Select.
3. Click Go to Special or press F5 on the keyboard and click Special…
4. The Go to Special dialog box will open. Select the radio button next to Formulas, and click Errors, then OK.

Excel will display all the errors on your Excel sheet; you can check and fix them one by one using the abovementioned tips. Press Ctrl + F and type #NAME? to spot errors quickly.

So, whether you use VLOOKUP, IF function, or pivot table, these tips can help you spot the #NAME? error & other problems with a formula, and quickly remove them from your sheet.

Also, keeping these things in mind while creating a new worksheet can minimize the risk of errors, thereby saving you time and effort.

Other than the wrong formula, if you face other Excel errors like 0x800ac472, it could be due to corrupted system files; read this guide to learn more.

If you are getting #NAME in Google Sheets indicating a Formula parse error, read this guide to learn about the solutions.

If you have any questions or suggestions about the subject, please mention them in the comments section below.