Are you wondering what does `#NAME?`

mean in Excel?

Read this Excel errors explainer until the end to learn all you need to know about `#NAME`

? error in Microsoft Excel.

At the end, you’ll learn the definition of the error, the reasons behind it, how to locate the errors in your worksheet, and how to avoid them completely. Let’s get started!

## What Does #NAME? Mean in Excel?

Ever come across a cell in your Excel spreadsheet displaying `#NAME?`

instead of a value or formula result? This cryptic error message might seem confusing at first, but it’s Excel’s way of telling you there’s a problem with a name it can’t recognize.

In Excel, names can refer to a few things: cell ranges (like `A1:B5`

), functions (like **SUM** or **VLOOKUP**), or even custom names you create for easier reference. The `#NAME?`

error pops up when a formula you’re using references a name that Excel can’t find.

ðŸ“’ **Read More**: What Does #SPILL! Mean in Microsoft Excel? [Causes & Fixes]

## Reasons for #NAME? Error in Excel

These are the root causes behind the `#NAME?`

error:

### Incorrect Function Spelling

Suppose, you prefer to type in an Excel function manually instead of using the **Excel Insert Function** tool. During typing, it’s common to make typing mistakes.

For example, in the above table, I’ve mistyped the **SUM** function to **SOM**.

Therefore, the incorrect formula `=SOM(B2:B7)`

generates the `#NAME?`

error when I hit the `Enter` button to calculate the total.

The correct formula with the syntax would be as shown below:

`=SUM(F2:F7)`

### Faulty Cell Range Reference

If you’re including a cell range reference in any formula syntax, the starting and ending cell addresses of the range should be separated by a colon. For example, the cell range reference for the **first 10 cells** in **column A** is `A1:A10`

.

If you do any of the following, the underlying function will generate the `#NAME?`

error:

- Forgot to use the colon to separate cell addresses.
- Using non-standard fonts or characters for
**A**,**B**,**C**, etc., column header alphabets to formulate the cell range reference.

The above screenshot should clear any doubts you might have regarding cell range referencing in a formula.

### Incorrect Reference of a Text

Whenever you construct a function syntax including a text reference, you must wrap the text in double quotation marks.

If you don’t include double-quotation, Excel won’t be able to process the text reference as a value for a function’s argument and this generates the `#NAME?`

error.

Functions where you often include a text as an argument’s value are **CONCATENATE**, **LEFT**, **RIGHT**, **MID**, **FIND**, **SEARCH**, **VLOOKUP**, **HLOOKUP**, **SUBSTITUTE**, and **IF**.

For instance, when writing an **IF** function that should display the text included in the formula, like **Pass**, must be within **double quotes**.

The following IF formula will generate the error:

`=IF(B2>40, Pass)`

However, this IF formula won’t show this error:

`=IF(B5>40, "Pass")`

The double quotes used to wrap the text reference in a formula must be in simple or straight double quotes. If you use curly or smart double quotes, Excel won’t be able to identify the character and will generate the error again.

### Referred Name Range Not Available

For ease of referencing, you might create **Named Ranges** for big datasets. Suppose, you haven’t yet created a **Named Range** you’re trying to enter or incorrectly typing its name, you’ll see the `#NAME?`

error.

For example, I’ve created a **Named Range** **Cost** for the cell range `B2:B7`

using the **Name Manager** command in Excel.

Now, I wanted to calculate the **SUM** of **Cost** in `B8`

.

Instead of entering it as **Cost**, I typed **Cast**.

Therefore, the following formula is generating the Excel name error:

`=SUM(Cast)`

Whether it’s an incorrect **Named Range** or a typing mistake, Excel considers this as a non-existent range and hence can’t calculate.

### Related Add-In Is Missing/ Inactive

Certain functions in Excel require the underlying add-in to calculate the value you’re looking for.

For instance, the Excel function **EUROCONVERT** will require an active **Euro Currency Tools** add-in.

However, the **Euro Currency Tools** add-in is often disabled by default after you install Excel for the Microsoft 365 desktop app. If you haven’t manually enabled the add-in, the function will show `#NAME?`

error.

### Entering an Unavailable Function

As Microsoft develops a newer Excel edition, it adds a few new functions to help users with data analytics, calculations, statistics, etc.

However, Microsoft might not always update the old Excel editions to make them backward compatible with these new functions.

For example, If you try to use the following functions in **Excel 2010** or older, you’ll get the `#NAME?`

error because those editions don’t know how to process such formulas.

**SORT****FILTER****UNIQUE****SEQUENCE****XLOOKUP**

### A Custom Function Is Missing

Microsoft Excel allows you to create your own functions using VBA. These are called **UDF** or **User Defined Functions**.

You can call these in cells after the equals sign in the same way as you do for any built-in function. In the suggestion box, the UDF function will show up. You can now click on that and enter argument values to do calculations.

However, if someone deletes the underlying VBA script module that defines the parameters for the UDF, the next time you reload or reopen the worksheet, you’ll get `#NAME?`

error.

ðŸ“’ **Read More**: How To Create Your Own User Defined Function With VBA

Other possible reasons for UDFs to generate the error are:

- Typos when writing the UDF function.
- The UDF function hasn’t been created yet.
- You’re trying to use the UDF in a general Excel workbook (
**XLSX**) instead of the macro-enabled Excel workbook (**XLSM**). - For Excel 365 business subscriptions, the IT admin might have disabled the VBA macro service.

For instance, I used a custom VBA script to create the **FACTORIAL** UDF in Excel. It works just fine as long as the script is available on the VBA Module for the workbook.

When I’ve deleted the VBA script from the workbook, Excel shows the `#NAME?`

error.

### The Source Contains the #NAME? Error

You might have written the formula syntax perfectly. However, if your formula refers to another cell range containing the error, you’ll see `#NAME?`

value in the cell. This is known as the cascading error.

Often, you simply copy and paste formulas from a third-party source. Or, you might remember a cell range reference from another worksheet or workbook and use that as is. So, before referring to a cell range in your formula, check the reference yourself.

### Incorrect Scope for a Named Range

**Named Ranges** can be created for a selected worksheet or for the entire workbook. Suppose, you’ve created a **Named Range** for `Sheet2`

and trying to refer to the same in a formula in `Sheet5`

. It’ll generate the `#NAME?`

error.

In the above example, the **Named Range** shown is only for `Sheet2`

. When I use it in the correct worksheet, it works.

However, when I try to refer to the same **Named Range** in a different sheet, it gives the error.

## How to Locate all #NAME? Errors in a Worksheet

You can use the following tools and techniques to pinpoint all `#NAME?`

errors in a worksheet or workbook:

### Using Find and Replace

Press `Ctrl` + `F` to bring up the **Find and Replace** dialog box.

In the **Find what** field, enter `#NAME?`

.

Click on the **Look in** drop-down menu and choose **Values**.

Click the **Find All** button.

The **Find and Replace** dialog box will extend below showing all the occurrences of the error throughout the worksheet.

The list will also show the formula so you can easily review and find the issues behind these `#NAME?`

errors easily.

Click on a result in the list to quickly navigate to the cell containing the formula.

Now, you can edit the formula to perfection.

### Using the Go To Tool

Press `Ctrl` + `G` on the worksheet to bring up the **Go To** dialog.

Click on the **Special** button.

In the **Go To Special** dialog box, click **Formulas** and uncheck all the checkboxes except **Errors**.

Click **OK**.

Excel will highlight all the formula errors in the worksheet including the `#NAME?`

error.

### Using Excel VBA

You can use this VBA script to create a VBA macro that’ll list all the `#NAME?`

errors, reasons, and suggestions in a new sheet in the source workbook:

```
Sub ListNameErrors()
Dim ws As Worksheet
Dim newWs As Worksheet
Dim newRow As Long
Dim cell As Range
Dim errorReason As String
Dim errorSuggestion As String
' Create a new worksheet in the active workbook
On Error Resume Next
Set newWs = ThisWorkbook.Sheets("NameErrors")
On Error GoTo 0
If newWs Is Nothing Then
Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
newWs.Name = "NameErrors"
End If
newRow = 1
' Loop through each worksheet in the active workbook
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.UsedRange
If IsError(cell.Value) And cell.Text = "#NAME?" Then
' Determine personalized error reason and suggestion
Select Case True
Case InStr(cell.formula, "undefined_function") > 0
errorReason = "Possible reason: The function name is misspelled or not defined."
errorSuggestion = "Suggestion: Check the spelling of the function or define it."
Case InStr(cell.formula, "invalid_reference") > 0
errorReason = "Possible reason: Invalid cell reference or range name."
errorSuggestion = "Suggestion: Verify the cell reference or range name."
Case Else
errorReason = "Possible reason: Formula references an undefined name."
errorSuggestion = "Suggestion: Check the spelling of the referenced name or define it."
End Select
' Write the error details to the new worksheet
newWs.Cells(newRow, 1).Value = ws.Name
newWs.Cells(newRow, 2).Value = cell.Address
newWs.Cells(newRow, 3).Value = errorReason
newWs.Cells(newRow, 4).Value = errorSuggestion
newRow = newRow + 1
End If
Next cell
Next ws
End Sub
```

You can find below the steps to create a macro using a VBA script:

ðŸ“’ **Read More**: How To Use The VBA Code You Find Online

Press `Alt` + `F8` to launch the **Macro** dialog, select **ListNameErrors** macro, and hit **Run** to execute the script.

Excel will create a new worksheet and list all the `#NAME?`

errors in one unified view.

## How to Avoid #NAME? in Excel

Follow these tools and tips to avoid errors in the future. Also, make sure you’re not using any IFERROR formulas to suppress the error.

### Using the Formula Suggestion Feature

Try to use the formula autocomplete feature to type the correct syntaxes of the functions you use in one or multiple formulas.

When you enter the equals sign and type in a few letters of the function, Excel will show a context menu in that cell. The context menu contains all possible functions matching those few letters.

You can **double-click** on the right function if you find that in the context menu.

If you don’t see the suggestion, it’s highly likely that the formula doesn’t exist in the current Excel edition.

Once you insert the function, Excel will show a small context menu to remind you of the function arguments. Arguments in the box parentheses are optional.

### Using the Insert Function Tool

If you’re new to Excel functions, you should practice using the **Insert Function** tool.

Suppose, you’d like to calculate totals in `B11`

for the above dataset.

Select `B11`

and click on the **Insert Function** command in the formula bar. It’s the **Fx** icon.

Type in the calculation or function you want to use. Click **GO** to search the Excel functions database.

Below the **Select a function** section, you should see a scrollable list of Excel functions to choose from.

Select **SUM** and click **OK**.

You’ll now see the **Function Arguments** dialog box with detailed instructions for the function arguments you must fill.

Follow the **on-screen instructions** in the dialog box and **review the details section** to construct the function.

Click **OK** to enter the function in the selected cell.

This action also calculates the function in the cell.

### Using the Excel Name Manager Tool

For **Named Range** referencing, you can often bring up the **Name Manager** tool by pressing `Ctrl` + `F3`.

The dialog box will show you all the available **Named Ranges** for the workbook or for individual worksheets in the Excel file.

You can look under the **Scope** column header in the dialog box to determine if the selected **Named Range** is for a specific sheet or the entire workbook.

Also, when creating a new **Named Range**, choose the **Workbook** option in the **Scope** drop-down menu. This will make the item available for referencing from any worksheet.

## Conclusions

Now you know what does `#NAME?`

mean in Excel and how to deal with the error efficiently.

If this article helped you learn this must-have Excel error-solving skill, you can acknowledge it below. Do you know a better way to handle the `#NAME?`

error in Excel? Add your expert tip in your comment.

## 0 Comments