#NAME? Error in Excel

MS Excel or Microsoft Excel is a powerful spreadsheet program. We can record large data sets in different cells within worksheets and perform desired calculations using functions or formulas. Excel has a wide range of built-in functions that help us get the calculated results. However, sometimes we get random unfamiliar codes instead of the desired results. These codes may vary from case to case and typically indicate something wrong with the applied formulas, data, or syntax. These random unexpected codes are called Excel errors.

The #NAME? is one such error in Excel that we may encounter while working with formulas. Like other Excel errors, the #NAME? error also occurs due to specific reasons, and we must perform the necessary steps to fix this error accordingly. This tutorial discusses the reasons why the #NAME? error may appear, how to recognize and resolve it in Excel.

What is the #NAME? error in Excel?

In Excel, the #NAME? error is one of the formula errors and occurs for various reasons. The main cause of this error is the mistakes made by users while applying formulas in a cell. According to the Microsoft official help section, the #NAME? error is Excel’s way of saying that there is something wrong or missing in an applied formula within the respective cell.

In most cases, the misspelled function or formula name causes the #NAME? error. However, there are many other reasons as well. Some such reasons causing the #NAME? error include incorrectly typed range name, misspelled cell range, missing quotation marks around the text in the formula, missing colon for a cell range, or incorrect formula version. Once the cause of the error is found, fixing a #NAME? error is usually just a matter of correcting spelling or syntax.

How to find #NAME? error in Excel?

Before we discuss the reasons and the steps to overcome the #NAME? error from our worksheet, we must first know to locate such errors so that we can perform the necessary steps to solve them.

If we have small data series in our sheet, we can navigate the entire sheet and easily locate the #NAME? error. We will see #NAME? code in the respective cell(s) instead of the expected calculated value. If we select the error cell, Excel also tells us what is wrong with the cell. In addition, the error cell displays a rectangular error box with an exclamation mark inside it.

If we have large data set in our Excel sheet, we cannot easily navigate the sheet to find the #NAME? errors. However, we can use any of the following methods to identify cells with the #NAME? errors:

Using the Go-To Special Tool

Using the Go-To Special tool of Excel, we can locate and select all the cells that have errors, including the #NAME? errors. That means the tool is not only limited to finding the #NAME? errors within the sheet. Instead, it highlights any type of Excel error our sheet may contain.

The following are the steps to use the ‘Go-To Special’ tool to find and select the cells with errors:

• First, we need to select the range of cells where we wish to search for the #NAME? error. We can select the entire datasheet by pressing the keyboard shortcut ‘Ctrl + A’.
• Next, we must navigate the Home tab, click the ‘Find and Select‘ tool under the Editing group, and choose ‘Go To Special‘ from the list. This will display the ‘Go-To Special’ window.
  
• In the ‘Go-To Special’ window, we need to select the Formulas radio button and mark the box associated with Errors. We must deselect all other boxes under Formulas, as shown below:
  
• Lastly, we must click the OK button, and the cells with errors will be selected instantly.

Once the error cells are located, we can go through each cell, find the desired error and fix it accordingly.

Using the Find & Replace Tool

The ‘Find and Replace’ tool works great when we only need to find the #NAME? errors within the sheet. Unlike the previous method, it does not select all the errors. Instead, it selects only the specified error.

The following are the steps to use the ‘Find & Replace’ tool to find and select the cells with #NAME? error:

• Like the previous method, we need first to select the data set where we want to locate the #NAME? error.
• Next, we need to go to Home > Find & Select > Find. This will open the ‘Find & Replace’ window with the Find tab selected. We can also use the keyboard shortcut ‘Ctrl + F‘ to open the ‘Find & Replace’ window. Under the Find tab, we must click the Options button to get the screen like this:
  
• We must type the #NAME? in the ‘Find What‘ box and select ‘Values‘ from the ‘Look in‘ drop-down list. Our window will look like this:
  
• After that, we must click the ‘Find All‘ button. This will display all the identified #NAME? errors in an additional box below the ‘Find & Replace’ window.
  
  We can select each error cell separately or select all these cells at once to carry out tasks like highlighting them, correcting related errors, or deleting them.

What are the reasons for #NAME? Error and how to fix this error?

When we apply any formula in our sheet and get the #NAME? error instead of the desired results, it means that there is something Excel cannot recognize. There can be several reasons that cause the #NAME? error and fixing such causes is just a matter of correcting the respective function name, spelling, or syntax.

Now, let us discuss some of the most common reasons that typically cause the #NAME? error and the steps needed to fix them to get our applied formula working again:

Misspelled or Invalid Function/Formula Name

One of the most common reasons for the #NAME? error in Excel is the misspelling of the used function name. In that case, Excel does not understand the function to perform the respective operation and causes the #NAME? error in the cell where the corresponding misspelled formula is applied. In addition to this, the error also occurs when we apply a function that does not exist.

For example, suppose we need to get the total of values used in a range B2:B8. The following formula to calculate the total will result in the #NAME? error.

=TOTAL(B2:B8)

In the above sheet, we can notice the #NAME? error in cell B9. Since the function name TOTAL is not a valid Excel function, it gives the error.

In another way, if we apply the valid function name incorrectly, the same error will occur.

In the above sheet, the SUMM function name is wrong. It should be spelled as SUM.

How to fix this?

We can fix this specific cause of #NAME? error by correcting the spelling of the used function and using the valid function name. In our example, if we change the spelling from SUMM to SUM, Excel displays the desired results in the cell.

Undefined or Misspelled Named Range

Creating a name for certain cells usually makes it easier to reference them within formulas. When referencing a name in the formula, the name must already be defined as a named range. If we use a name in a formula without defining it in the selected sheet, Excel returns the #NAME? error in a respective cell. In addition to this, the error also occurs when we use a defined name in the formula but mistakenly enter the wrong spelling.

For example, suppose we need to get the total of values used in a range B2:B8. The following formula to calculate the total will result in the #NAME? error.

=SUM(Quantity)

In the above sheet, we can notice the #NAME? error in cell E1. Since the Quantity is not defined as a named range, Excel gives the error.

In another way, if we have named the range B2:B8 as Quantity but use the wrong spelling, the same error will occur.

In the above sheet, the error occurs because a named range spelling Quanty is wrong. It should be spelled as a defined name Quantity.

How to fix this?

We can fix this specific cause of #NAME? error by defining a named range before using it in formulas or correcting the spelling of the named range used in the formulas. In our example, if we change the spelling for a named range from Quanty to Quantity as defined, Excel displays the desired results in the cell.

It is essential to note that a defined name for a range of cells is not the same as the column header. A named range must be defined via Formulas > Name Manager, and the column header must not be included in the selected range.

Incorrect Range or Missing Colon

When defining a range in formulas manually, there is a possibility that we may enter it incorrectly. This can result in the #NAME? error in that corresponding cell. Generally, we may enter the wrong combination of letters or numbers while defining a range. Also, we may forget to add a colon that is used to separate two or more cell names/references.

For example, if adding values of range B2:B8 using the below formula, Excel will produce #NAME? error:

=SUM(B2:BB)

In the above sheet, we can notice the #NAME? error in cell B9 because of the wrong combination of letters and numbers. The range must be B2:B8, not B2:BB.

In another way, if we have entered the proper range but missed a colon, the same error will occur.

How to fix this?

Correcting the combination of letters and numbers in a used range or adding a colon between the cell names in a used range fixes the #NAME? error and provide the expected results. In our example, if we correct the range with a colon, Excel displays the desired results in the cell.

Missing Quotes or Smart Quotes around the Text in Formulas

Many Excel functions allow users to include the necessary text to get the desired results. When entering a text value in a formula, there may be cases when we get the #NAME? error. The error usually occurs when we forget to include double quotes to enclose the text within the formula. We must always enclose the text in double quotation marks, even if we use only a space.

If we do not use double quotes to enclose the text in a formula, Excel thinks or tries to interpret the respective value as a function name or named range. This causes the #NAME? error since no match is found for text as a function or named range when used without quotes.

When adding the double quotes around the text, we must use the straight double quotes (” “). If we use smart or curly quotes (” “), Excel will not interpret them as valid quotes and still provide the #NAME? error.

For example, if we try to calculate the length of the text ‘Excel’ using the below formula, Excel will produce #NAME? error:

=LEN(Excel)

In the above sheet, we can notice the #NAME? error in cell B2 because of the missing quotes in the text Excel. Also, the corresponding text/word is not a defined name or variable.

In another way, if we have entered the smart or curly quotes, the same error will occur.

How to fix this?

Adding the proper double quotes around the text or string fixes the #NAME? error and provides the expected results. In our example, if we include double quotes around the text Excel, the function returns the calculated length or result.

Excel may automatically recognize the missing quote error for some formulas and display a pop-up asking for the corresponding error to be corrected. The pop-up window also displays a relevant suggestion that we can accept or reject accordingly.

Opening/Adding New Version Formulas in Older Versions

Excel has several built-in functions. It has received many new functions with major upgrades compared to its previous versions. For example, functions like CONCAT, SWITCH, TEXTJOIN, XLOOKUP, FILTER, SORTBY, etc., are available in Excel 2019 and Excel 365. Likewise, some functions were included in Excel 2016 and 2013 but were missing in previous versions like Excel 2010 and prior versions.

Unfortunately, the functions introduced in any upgraded version of Excel do not work on older versions. If we try to apply such newly added functions in older versions or open a workbook with newer formulas in an older version of Excel, we will get the #NAME? error. The error appears because the corresponding functions are not present in that older version of Excel. Such new functions are interpreted as misspelled names.

For example, if we apply the IFNA function in Excel 2010, we see the #NAME? error.

How to fix this?

We can fix this specific cause of #NAME? error by upgrading the Excel version to the latest available version. Unfortunately, there is no other fix to make that newest version formula work in the old version of Excel. In the case of our example, if we use the IFNA function in Excel 2019, the error is resolved, and the result is given.

Additionally, if we try using the ‘Save As’ feature to save a workbook that contains macro with formulas, we must enable the Macros in the newly saved file. If we don’t do this, Excel may return the #NAME? error.

Avoiding #NAME? Error in Excel

We have already discussed the most common reasons that cause #NAME? errors in an Excel sheet and the ways to fix them accordingly. However, it is best to follow some basic tips and techniques to avoid this error before it happens and increase work productivity, saving a significant amount of time.

The following are the best ways to prevent #NAME? errors in Excel:

Using the Formula Assistance

In most cases, the #NAME? errors appear because of misspelled functions. Therefore, we must avoid typing the functions manually. Instead, we can take help from the formula assistance feature of Excel. Whenever we type an equal sign and start entering the function or formula name, Excel automatically lists all the relevant matching names accordingly.

Instead of typing the whole function/formula, we must select the desired one from the list. We can use the arrow keys to move up and down between list names. Excel tells or describes the use of the selected function. Once we have the desired function or formula, we should press the TAB key on the keyboard. It automatically enters the corresponding function/formula in the selected cell, ensuring that the corresponding name is not misspelled. Additionally, Excel also displays the syntax (range, criteria) of the selected function.

Using the Formula Wizard

Another best option to avoid the #NAME? error is to use Excel’s Function Wizard while entering the formula within the cell(s). It is mainly useful when we are not sure of the arguments required by the selected function, which can result in #NAME? error. The Formula Wizard helps us generate valid functions, displaying the syntax with the supported range, criteria, or arguments.

To use the Formula Wizard, we need first to select a cell to insert the desired formula. After that, we must go to the Formulas > Insert Function under the Function Library group. This will launch the ‘Insert Function’ window. We can also click the Function Wizard button ‘fx‘ from the toolbar before the formula bar.

In the Insert Function window, we can click on the drop-down list next to ‘Select a category‘ and any desired category to get the corresponding functions. After selecting the category, we can select the desired function from the box under ‘Select a function‘. We can also find the desired function using the Search box from the top of the window. We must select the desired function and click the OK button. After that, Excel displays the ‘Function Arguments’ window that helps us define respective arguments properly.

In the above image, we notice that Excel displays descriptions for each corresponding argument, allowing us to easily specify them accordingly. Once we have specified all the arguments, we can also check the results obtained from the applied function from the bottom of the Function Arguments window. If we are happy with the results, we can click the OK button to insert the respective function into the selected cell in the sheet.

After the desired function is inserted into the sheet, we can see the results in the respective cell and the applied formula in the formula bar.

Using the Name Manager

Excel also allows us to create a named range for certain cells, and using this wrongly within a formula may result in #NAME? errors. Therefore, we must avoid typing named ranges manually. Instead, we must use the Name Manager when working with many named ranges or tables. It helps us view all the names ranges and table names we created in a respective sheet. We can select desired named range, add a new one, edit or delete it accordingly.

To access the Name Manager tool in Excel, we need to navigate the Formulas tab and click on the ‘Name Manager‘ button under the Defined Names group.

This will open a Name Manager window to get the details of all the existing named ranges.

In the above image, we can notice the New, Edit, and Delete buttons to help us perform the respective task. We can also press the keyboard shortcut ‘Ctrl + F3‘ to quickly launch the Name Manager.

When writing any formula in an Excel sheet and need to use the defined name range, we can also go to the Formulas > Use in Formula to view all the named ranges. We can click on the desired named range and ensure that the desired named range is not misspelled.

Important Points to Remember

• We can use the IFERROR function to encounter any Excel formula error, including the #NAME? error.
• We can use ERROR.TYPE formula to find the specific type of error.