*Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Web App, Excel Starter 2010.*

This guide will outline why the #NAME? error appears in your formulas and how to rectify this.

The single greatest reason why the #NAME? error displays in your formula is due to a typo being found in the formula name. Check the following example:

**Important:** The #NAME? error emphasises that an element within the syntax must be rectified. Therefore, once you notice the error in your formula, fix it immediately. Resist the temptation to use any error-handling functions like IFERROR to conceal the error.

Use the Formula Wizard in Excel to evade formula names from getting typos. After you begin entering a formula name in a cell or the Formula Bar, a list of formulas corresponding to your exact search terms are presented in a dropdown. When you input the formula name and the opening parentheses, the Formula Wizard shows the syntax as hover text.

You can even apply the Function Wizard to bypass the syntactical errors. Pick the cell including the formula. Then, on the **Formula** tab, press **Insert Function**.

Excel will instantly launch the Wizard for you.

As you select each argument, Excel will provide you with the relevant information for each one.

Listed below are alternative causes of the #NAME? error.

## The formula refers to a name that has not been defined – results in the #NAME? error

Once your formula has a reference to a name that is undefined in Excel, you will notice the #NAME? error.

Look at the following example of a SUM function, with the data subject being **Profit**, which is an uncategorised workbook name.

**Solution**: Use **Name Manager** to define a number. Next, relay the name to the formula. This can be achieved by following these steps:

- If you already have the data in the spreadsheet, and want to categorise a name to particular cells or a cell range, start by choosing the cells within the spreadsheet. Conversely, if you seek to produce a new range, you can jump to the next step.
- Press
**Define Name**, and then click**Define Name**. Do this in the**Formulas**tab, within the**Defined Names**group. - Type a unique name.
- For the
**Scope**, pick if you prefer the name to solely be available inside the sheet. Alternatively, you can confirm the whole workbook. - Type an optional comment.
- Press
**OK**. The next part is to place the name in the formula. - Leave the cursor in the formula syntax at the stage where you wish to relay your newly created name.
- Select the
**Formulas**tab, in**Defined Names**group. Then, click**Use in Formula**. After this, pick your target defined name that you seek to add.

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

## The formula has a typo in the defined name – causes the #NAME? error

If the syntax mistakenly refers to a defined name, you will notice the #NAME? error.

Carrying on with the past example, a defined name for **Profit** was formed in the spreadsheet. In the following example, the name is misspelt which results in the function coming up with the #NAME? error.

**Solution**: Fix the syntax typo and attempt running the formula again.

**Tip:** Rather than physically typing defined names in formulas, you can configure Excel to conduct intuitively for you. Do the following: visit the **Formulas** tab, in **Defined Names** group, then select **Use in Formula**. Next, pick your preferred defined name to insert. Excel will assign the name to the formula.

## The syntax is missing double quotation marks for text values – causes the #NAME? error

After you embody text references in formulas, you must contain the text in quotation marks, irrespective of if you’re solely using a space. If the syntax ignores double quotation marks “” for a text value, you will notice the #NAME error. Have a look at the following example:

The syntax in this example has omitted double quotation marks for **has**; that’s why there is an error in the first place.

**Solution**: Return to your syntax and manually check to guarantee that any text values are enclosed with quotation marks around them.

## A colon was omitted in a range reference

If you forget a colon in a range reference, the formula will present a #NAME? error.

In the following example, the INDEX formula shows the #NAME? error because the B2 to B12 range has excluded a colon.

**Solution**: Inspect your syntax to check that all range references contain the colon.

## You are using a function that requires a specific add-in that is not enabled in Excel

There are some Excel functions that work conditionally on the basis that specific add-ins are activated. Otherwise, utilising these functions will generate a #NAME? error. For example, to apply the EUROCONVERT function, the **Euro Currency Tools** add-in must be toggled on. If you employ custom functions or macros that need the Analysis ToolPak, ensure that the **Analysis ToolPak** add-in is activated.

To activate add-ins in Excel:

- Choose
**File**>**Options**. - Select
**Add-ins**. - In the
**Manage list**box, click**Excel Add-ins**and press**Go**. - Tick the relevant box and press
**OK**.

## Do you have a specific function question?

Post a question in the Excel community forum

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

## Similar Error Topics

Excel functions (alphabetical)

## Other Links

Back to Software Bytes – Official Website