Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

The top reason why the #NAME? error appears in the formula is because there's a typo in the formula name. Look at the following example:

#NAME? error caused by a typo in syntax

Important: The #NAME? error signifies that something needs to be corrected in the syntax, so when you see the error in your formula, resolve it. Do not use any error-handling functions such as IFERROR to mask the error.

To avoid typos in formula names, use the Formula Wizard in Excel. When you start typing a formula name in a cell or the Formula Bar, a list of formulas matching to the words you've entered displays in a dropdown. Once you enter the formula name and the opening parentheses, the Formula Wizard displays the syntax as hover text.

Formula syntax as hover text

You can also use the Function Wizard to avoid the syntactical errors. Select the cell with the formula, and on the Formulas tab, press Insert function button Insert Function.

Excel automatically loads the Wizard for you.

Example of the Formula Wizard dialog.

As you select each argument, Excel gives you the appropriate information for each one.

Below are other causes of the #NAME? error.

When your formula has a reference to a name not defined in Excel, you see the #NAME? error.

See the following example of a SUM function referring to Profit, which is an undefined name in the workbook.

#NAME? error caused by a reference to an undefined name in the syntax

Solution: Define a name in Name Manager, and then add the name to the formula by following these steps:

  1. If you already have the data in the spreadsheet, and you want to assign a name to specific cells or a cell range, first select the cells in the spreadsheet. If you want to create a new range, you can skip this step.

  2. Go to Formulas > Defined Names > select Define Name, and then select Define Name.

  3. Enter a unique name.

  4. For the Scope, select if you want the name to be available within the sheet only, or the entire workbook.

  5. Enter an optional comment.

  6. Select OK.

    The next steps are to add the name in the formula.

  7. Keep the cursor in the formula syntax at the point where you want to add the name you just created.

  8. Go to Formulas > Defined Names > select Use in Formula, and then select the defined name you want to add.

    Click "Use in Formula" to add a defined name to a formula syntax

For more information on using defined names, see Define and use names in formulas.

If the syntax incorrectly refers to a defined name, you see the #NAME? error.

Continuing with the previous example, a defined name for Profit was created in the spreadsheet. In the following example, the name is not spelled correctly, so the function still throws the #NAME? error.

#NAME? error caused by a typo in the defined name in the syntax

Solution: Correct the typo in the syntax and retry the formula.

Tip: Instead of manually entering defined names in formulas, you can have Excel do it automatically for you. Go to Formulas > Defined Names > select Use in Formula, and then select the defined name you want to add. Excel adds the name to the formula.

When you include text references in formulas, you need to enclose the text in quotation marks, even if you’re only using a space. If the syntax omits double quotation marks “” for a text value, you see the #NAME error.

See the following example.

#NAME? error caused by missing double quotation marks in text values

The syntax in this example is missing double quotation marks for has; that explains the error.

Solution: Go back to your syntax and manually inspect to make sure that any text values have quotation marks around them.

If you miss a colon in a range reference, the formula displays a #NAME? error.

In the following example, the INDEX formula throws the #NAME? error because the B2 to B12 range is missing a colon.

#NAME? error caused by absence of colon in a range reference

Solution: Check your syntax to make sure all range references include the colon.

There are some Excel functions that work only when certain add-ins are enabled. Using these functions otherwise produces a #NAME? error. For example, to use the EUROCONVERT function, the Euro Currency Tools add-in must be enabled. If you use custom functions or macros that require the Analysis ToolPak, make sure that the Analysis ToolPak add-in is enabled.

To enable add-ins in Excel:

  1. Go to File > Options.

  2. Select Add-ins.

  3. In the Manage list box, select Excel Add-ins, and then select Go.

  4. Check the relevant box and select OK.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Excel functions (alphabetical)

Excel functions (by category)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×