How To Deal With The #NAME Error In Excel

  • Post author:
  • Post category:Uncategorized

We will achieve the #NAME error in any event where Excel cannot recognize something in our formula or function. The most common occurrence is when we have a misspelled function but there are many other causes.

We fix the #NAME error mainly by correcting spelling or syntax. In this tutorial, we will explore some popular #NAME error situations and how to fix them.

Figure 1 – Name error in Excel

#NAME Error – Typo in the Entered Formula

In order to avoid Typos in formula names, it is important that we use the Formula Wizard in Excel. Once we enter a formula name, the formula Wizards helps direct the steps by matching the formula name, range and values. To illustrate, we will use the Formula Wizard to find the SUM of a list of times.

  • We will go to the Formula Tab and click the Insert Function

Figure 2 – Fixing the #name error in excel

  • We can either enter the description of the function, or select a category in the Insert Function window

Figure 3 – fixing the invalid name error in excel

  • When we scroll and find the function, Excel would automatically load the Wizard for us where we can insert each argument for our calculations.
  • Each time we enter the argument, Excel will give the appropriate information for each one.

Figure 4 – Using the Formula wizard to fix excel #name? error

#Name Error – Undefined Named Range

When there is a typo in the defined name or the defined name syntax is incorrect, then we will see the #NAME error.

Figure 5 – Excel #name error

A good way of eradicating this error is by going to the Formula Tab and select Define name. Next, we will enter the name of the Range and Cell reference in the New Name window.

Figure 6 – #name excel meaning

Again, we will go to Formulas and select Cost from the drop-down list of the Use in Formula button.

Figure 7 – #name error in excel  

When we define the name of the range, the name error will be removed

Figure 8 – #Name error fixed

Misspelled Function Name

If we have incorrectly spelt the function name, we may also have the #NAME error. In this case, we incorrectly spelt the COUNTIF function.

Figure 9 – Excel #name error

If we correct the mistake, we will have this result

Figure 10 – Fixed #Name error

Omitted Quotation or Colon marks

In some cases, we may forget particular references with colons or insert the appropriate for range references, when such events occur, the formula will display the #NAME error. The solution is to go back and inspect the formula.

Figure 11 – Invalid name error in excel

When we have checked our formula, we will find the error. In this case, our cell reference was not entered correctly. If we correct it, we will have:

Figure 12 – Fixed #name error

Special Add-ins not enabled

Some functions and macros may require specific add-ins that has not been enabled by default in Excel. We should first find out if our function requires a special add-in. Next, we can enable the add-in by:

  • Go to File and select Options
  • Next, we will click Add-ins
  • In the Manage List window, we will click Excel Add-ins and select Go
  • Next, we will mark the relevant box and select OK.

Figure 13 – Using Excel Add-ins to fix name error.