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.