What to do if there is a NAME error when entering a formula in Excel

What to do if there is a NAME error when entering a formula in Excel
What to do if there is a NAME error when entering a formula in ExcelWhat to do if there is a NAME error when entering a formula in ExcelThe top reason why the #NAME? error appears in the formula is because there is a typo in the formula name. Look at the following example:

What to do if there is a NAME error when entering a formula in Excel

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 have entered displays in a dropdown. Once you enter the formula name and the opening parentheses, the Formula Wizard displays the syntax as hover text.

What to do if there is a NAME error when entering a formula in ExcelYou can also use the Function Wizard to avoid the syntactical errors. Select the cell with the formula, and on the Formula tab, press Insert Function.

Excel will automatically load the Wizard for you.

What to do if there is a NAME error when entering a formula in ExcelAs you click each argument, Excel will give you the appropriate information for each one.

Given below are other causes of the #NAME? error.

The formula refers to a name that has not been defined

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

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

What to do if there is a NAME error when entering a formula in ExcelSolution: Define a name in Name Manager, and then add the name to the formula. Follow these steps to do that:

  1. If you already have the data in the spreadsheet, and 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. On the Formulas tab, in the Defined Names group, click Define Name, and then click 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. Click OK.The next part is 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 the Formulas tab, in Defined Names group, click Use in Formula, and then select the defined name you want to add.What to do if there is a NAME error when entering a formula in Excel

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

The formula has a typo in the defined name

If the syntax incorrectly refers to a defined name, you will 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.

What to do if there is a NAME error when entering a formula in ExcelSolution: 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. To do that, go to the Formulas tab, in Defined Names group, click Use in Formula, and then select the defined name you want to add. Excel will add the name to the formula.

The syntax is missing double quotation marks for text values

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 will see the #NAME error. See the following example.

What to do if there is a NAME error when entering a formula in ExcelThe syntax in this example is missing double quotation marks for has; that’s why the error.

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

A colon was omitted in a range reference

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

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

What to do if there is a NAME error when entering a formula in ExcelSolution: Check your syntax to make sure all range references include 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 only when certain add-ins are enabled. Using these functions otherwise will produce a #NAME? error. For example, to use the EUROCONVERT function, the Euro Currency Tools add-in has to 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. Click File > Options.
  2. Click Add-ins.
  3. In the Manage list box, pick Excel Add-ins and click Go.
  4. Check the relevant box and click OK.

For more such interesting article like this, app/softwares, games, Gadget Reviews, comparisons, troubleshooting guides, listicles, and tips & tricks related to Windows, Android, iOS, and macOS, follow us on Facebook, Instagram, Twitter, YouTube, and Pinterest.


Advertising statement: The external jump links (including but not limited to hyperlinks, QR codes, passwords, etc.) contained in the article are used to convey more information and save selection time. The results are for reference only. All Naijatechnews articles include this statement.


Article Editor in Chief: Coker | I want to correct article mistakes.


Click to see more posts about ūüĎá

Recommended Gists





Be the first to comment

Leave a Reply

Your email address will not be published.