Why am I getting a #NAME? error in Microsoft Excel?

excel-logo.jpg

Updated: 06/30/2019 by Computer Hope

When creating formulas in Microsoft Excel, you may see a #NAME? error in a cell. There are multiple reasons why this error occurs. Some of the causes may seem obvious, but they are due to mistakes that you or anyone else can easily make.

Click each link below for information about the most common causes for the #NAME? error.

Incorrect function name in a formula

If a function name is spelled wrong or does not exist in Microsoft Excel, the #NAME? error is displayed in the cell where the formula is entered.

Misspelled formula name in Excel

For example, if you entered the following formula, it would result in the #NAME? error.

=SUMM(A1:A15)

The SUMM function name is spelled incorrectly. Instead, it should be spelled SUM.

Invalid formula name in Excel

Another example is the use of a function that doesn’t exist in Excel.

=TOTAL(A1:A15)

The function name TOTAL is not a valid function, resulting in the #NAME? error.

How to fix the error

Correcting the spelling of the function name and using a valid function fixes the issue and allows the formula to display a value in the cell.

Missing colon for cell range in a formula

When referencing a range of cells in a formula, a colon must be entered between the two cell names. Without a colon, the formula generates the #NAME? error.

Missing colon for cell range in an Excel formula

For example, the formula below is trying to add the values of cells A1 through A15.

=SUM(A1A15)

Unfortunately, that formula will generate the #NAME? error because there is no colon between A1 and A15.

How to fix the error

Adding a colon between the two cell names, A1 and A15, fixes the issue and allows the formula to display a value in the cell.

Undefined name in a formula

Creating a name for a range of cells can make it easier to reference in a formula. Without a defined name, a formula must explicitly reference the range of cells, like D2:D13. If the cell range changes, you have to update each formula that references that range of cells, whereas you do not if the range has a name.

Undefined name in Excel

When referencing a name in a formula, it must first be defined. If the name is not defined in your spreadsheet, you will see the #NAME? error in the cell with the formula.

Note

A defined name for a range of cells is not the same as a column header. For example, in the picture, there is a “Sales” column header, which is not a defined name for the cells below that header. If you have a column header, you still must define a name for the cells below that header if you want to use that defined name in a formula.

For example, the formula below references a cell range name of Sales, but that name is not defined.

=SUM(Sales)

Because Sales is not defined, the formula generates the #NAME? error.

How to fix the error

Defining a name for the range of cells (Sales in our example above) fixes the issue and allows the formula to display a value in the cell.

Misspelled name in a formula

Misspelled name in Excel

If a name is defined for a range of cells, that name must be spelled correctly when used in a formula. If the name is spelled wrong, the formula generates the #NAME? error.

For example, the formula below references the Salse name.

=SUM(Salse)

The issue is that the range of cells is named Sales and not Salse. Because the cell range name is spelled incorrectly in the formula, the #NAME? error is displayed.

Note

A defined name for a range of cells is not the same as a column header. For example, in the picture, there is a “Sales” column header, which is not a defined name for the cells below that header. If you have a column header, you still must define a name for the cells below that header if you want to use that defined name in a formula.

How to fix the error

Correcting the spelling of the defined cell range name fixes the issue.

Missing double quotes around the text in a formula

An Excel formula may contain text to be included in the value displayed by that formula. When entering text in a formula, it needs to be enclosed in double quotes. If the text is not enclosed in double quotes, it is considered part of the formula calculation and results in the #NAME? error.

Missing double quotes around text in Excel formula

For example, the formula below is concatenating text with a cell value.

=CONCATENATE("Total sales in ",C2,were,D2)

Double quotes are used to enclose the text “Total sales in” in the formula, but the double quotes were missed around the text were. Without the second set of double quotes, the formula tries to execute were as if it is a defined name or variable. There is no were defined name or variable, resulting in the #NAME? error.

How to fix the error

Adding double quotes around the text in the formula, like were in our example above, fixes the issue and allows the formula to display the concatenated value in the cell.

PinIt
submit to reddit

Leave a Reply

Your email address will not be published. Required fields are marked *

Top
Web Design BangladeshWeb Design BangladeshMymensingh