Share this article

Improve this guide

How to Fix the #NAME Error in Excel

First, check the Function name spelling

5 min. read

Updated onOctober 7, 2024

updated onOctober 7, 2024

Share this article

Improve this guide

Read our disclosure page to find out how can you help Windows Report sustain the editorial teamRead more

Key notes

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 theFormula Wizard. Whenever you type a formula name in a cell orFormula 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 theInsert functionnext to theFormula 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:

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 theEuroconvertfunction. This function needs you to enable theEuro Currency Toolsadd-in.

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

This will help you use the custom functions; however, you must disable the ones you don’t use to prevent issues likeExcel has run into an errorandfile 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?

Excel will display all the errors on your Excel sheet; you can check and fix them one by one using the abovementioned tips. PressCtrl+Fand 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 otherExcel 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. Moreover, we can help you out if theYEAR function isn’t workingcorrectly.

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

How to Fix the #NAME Error in Excel#

How do I fix the #NAME error in Excel?#

1. Check the function name spelling#

2. Invalid named range#

3. Double Quote missing from the text value#

4. Using the new function in the older Excel version#

5. Wrong range reference#

6. Custom function is not available#

7. Check for a missing colon#

How can I find #NAME? errors in Excel?#