Search results
Jun 28, 2017 · 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).
- How to correct a #NAME? error - Microsoft Support
If you miss a colon in a range reference, the formula...
- How to correct a #NAME? error - Microsoft Support
- 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 · This error occurs when Excel cannot recognize the text in the formula, usually due to a typo, incorrect function names, or references to undefined names. Fortunately, the #NAME? error is easy to fix once you know the causes and solutions.
Jan 9, 2023 · Several people are reporting that some cells with those formulas are changing to #NAME? at random times, instead of showing the result of the formula. This actually happened to me several time as I was creating it, and for that I was using Excel for Windows.
- Misspelled Function Names. When entering Excel functions, we have to use the correct spelling of the function name. If we make a typo, we will get a #Name Error.
- Misspelled Function Arguments. Name errors can also occur when you have misspelled argument names in the functions. In the following example, the VLOOKUP function is used in cell E2 to get the age of the selected person in cell D2.
- Using Undefined Names. The use of undefined names in Excel calculations is another very common source of #Name errors. The table below shows a student’s scores for three subjects.
- Spelling Errors When Using Defined Names. Let’s say that we have a defined name as “Scores” in our Excel workbook. In the table below, we used the defined name to calculate a student’s average score.
One of the most common reasons people see the name error is when they have used the wrong formula name. For example, if you’re using the VLOOKUP formula and you type VLOKUP instead, Excel wouldn’t know what you mean, and it will show its disapproval by giving you the name error.
People also ask
How do I display '#name' instead of entered text in Excel?
What is a formula name error in Excel?
Why do I get a name error in Excel?
How do I insert a formula in Excel?
How to fix error in Excel formula?
How to fix a cell with a name error in Excel?
Aug 28, 2024 · To avoid the #NAME error: Use the Excel formula wizard to enter function names. Enter the names of ranges/tables from the drop-down list of names launched by Excel. Double-check the text arguments of your formula to be enclosed in double quotation marks.
