Search results
Jun 28, 2017 · I've made sure that every single cell in the table is formatted as text. However, certain cells are displayed as "#NAME?" and under the exclamation mark next to them, there is a text saying "The formula contains unrecognized text.". Two examples of the cells displaying "#NAME? instead of the entered text are: -goer and -fold (including the dashes).
- Overview
- The formula refers to a name that has not been defined
- The formula has a typo in the defined name
- The syntax is missing double quotation marks for text values
- A colon was omitted in a range reference
- You are using a function that requires a specific add-in that is not enabled in Excel
- Need more help?
- See Also
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:
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.
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.
Excel automatically loads the Wizard for you.
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.
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.
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.
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.
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.
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.
You can always ask an expert in the Excel Tech Community or get support in Communities.
Sep 28, 2024 · Solution: Go to Formulas > Name Manager to check if the named range exists. If the name is missing, define the range by clicking on New and entering the appropriate range reference. Here’s an example of checking for named ranges in Excel: Step. Action. 1. Go to the Formulas tab. 2.
Click the Number tab and then, under Category, click Custom. In the Type box, enter ;;; (three semicolons), and then click OK. Click OK again. The 0 in the cell disappears. This happens because the ;;; custom format causes any numbers in a cell to not be displayed. However, the actual value (0) remains in the cell.
Aug 28, 2024 · When you’ve found the relevant function, press the “Tab” key. This enters the complete name of the first function on the drop-down list into the active cell. This saves you the chance to misspell the name of the function. And you can safely continue to enter the rest of the arguments. Pro Tip!
To overcome this error, we have to follow the below steps. Select the cell or the range to define a name. Go to the “Formulas’ tab. Click “Define Name” in the “Defined Names” group. Enter a name in the “Name” box and click “Ok”. When we click the “OK” button, we will get the average score in cell E2.
People also ask
How do I display '#name' instead of entered text in Excel?
How to check if a formula has errors in Excel?
How do I assign a name to a cell in Excel?
How do I insert a formula in Excel?
How do I format a number in Excel?
How to fix a cell with a name error in Excel?
May 10, 2023 · On the Home tab, in the Editing group, click Find & Select > Go to Special. Or press F5 and click Special… . In the dialog box that appears, select Formulas and check the box for Errors. Click OK. As a result, Excel will select all cells within a specified range that contain errors, including #NAME.
