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

Updated: 09/03/2019 by Computer Hope
Microsoft Excel logo

When creating formulas in Microsoft Excel, you may see a #NAME? error in a cell. There are multiple reasons why this error occurs. Some causes may seem obvious, but anyone can easily make these mistakes.

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, 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 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 generates 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 must update each formula referencing 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 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, the picture shows a "Sales" column header, which is not a defined name for the cells below that header. You must define the cells name below the column header to use them it 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.

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, the picture shows a "Sales" column header, which is not a defined name for the cells below that header. You must define the cells name below the column header to use them it 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 word in Excel formula

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

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

Double quotes 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's 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 text in the formula, like our above examples, fix the issue and allows the formula to display the concatenated value.