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.

  • Incorrect function name in a formula.
  • Missing colon for cell range in a formula.
  • Undefined name in a formula.
  • Misspelled name in a formula.
  • Missing double quotes around the text in a formula.

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.

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.

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.

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.

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.

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

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.

=SUM(Sales)

Because Sales is not defined, the formula generates the #NAME? 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

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.

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.

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.

Adding double quotes around text in the formula, like our above examples, fix the issue and allows the formula to display the concatenated value.

  • Help, examples, and information on Excel formulas.
  • Getting #DIV/0! in Microsoft Excel spreadsheet.
  • How to define a name for a range of cells in Microsoft Excel.
  • Type an equal sign in a spreadsheet without doing a formula.
  • How to copy and paste text and formulas in an Excel spreadsheet.
  • Why Excel shows ##### in a spreadsheet cell.
  • Microsoft Excel help and support.