How to Fix the #NAME Error in Excel?

Have you ever tried working an extremely lengthy formula, made a tiny bit of an update, and pressed Enter only to get the #NAME error? It can be pretty nerve-racking, right? 

In this article, I’ll show you all the possible causes for the #NAME error and the steps you could apply to correct it.

What causes the #NAME error in Excel?

The #NAME error is Excel’s way of telling you that there is a text inside your formula that it does not recognize.

It could be caused by:

• a typo in the function name
• a syntax error                                     
• an invalid range address
• a reference name that does not exist
• a missing macro or add-in
• incompatible Excel version

Unlike other Excel errors, the #NAME error should be addressed and not just masked with error-handling functions like IFERROR(). Otherwise, your data could end up with inaccurate values.

Fix the #NAME Error

The first step in fixing the #NAME error in Excel is to identify what particular text inside the formula is causing the error.

To do this:

1. Select the cell that contains the #NAME error.

2. Go to Formulas >> (Formula Auditing Section) >> Evaluate Formula

3. The Evaluate Formula menu will appear. You should see your formula inside its textbox.

4. Click on the Evaluate button. Notice that it processes the formula one section at a time.

5. Click on the Evaluate button a couple more times until you see the #NAME error.

6. Take note of where the #NAME error appeared in your formula.

7. You may need to redo the steps if you’ve missed the text where the error occurred.

Once you’ve identified the text that caused the error, the next step is to apply the appropriate solution.

1. If the function name is invalid

There are three reasons why a function name is considered invalid:

• It has a typo.
• It requires a particular add-in or macro.
• It is not compatible with your version of Excel.

Of course, if the function name has a typo, you only need to correct it.

To check the spelling of a function, you could take advantage of Excel’s Formula Wizard. To do this:

1. Select a blank cell.

2. Type an equal sign ‘=’ followed by the Excel function.

3. Notice that as you type in the name, the Formula Wizard provides a list of Excel functions that might be what you intend to use.

4. Select from the list and add in the arguments, if needed. Once that is done, press Enter.

If you did not encounter any error, copy the formula and paste it onto your original cell.

If your Excel function doesn’t have any match from the Formula Wizard’s list of suggestions, it could be that:

• It only works on selected versions of Excel.
• It requires an Excel add-in.
• It is a customized function that requires a macro for it to work.

If you want to double-check to see if it indeed is not available (and not just a typo), you could:

1. Select a blank cell.

2. Go to Formulas >> Insert Function.

3. The Insert Function menu should appear. Type part of the function name inside the “search textbox” and press Enter.

4. All possible matches will appear in the dropdown list.

5. If it does not match any of the Excel functions, you may want to ask your colleagues if the formula works on their computers.

If it works on them, but not on yours, then it’s likely that it is not compatible with your version of Excel. Or you might be just missing an add-in or macro.

Ask them for their version of Excel and see if they installed an add-in or added any macro to run it.

Install the add-in or add the macro, if necessary. Then test it out again.

If these two options do not work, you may need to upgrade Office.

If you can’t afford to do that and you’re short on time, you could, in the meantime, borrow a computer with the same version of Excel as your colleagues and work on the data.

2. If the range is invalid

A range can be considered invalid for the following reasons:

• The range argument has a typo; or
• The specified named range no longer exists.

Look into each of the Range Addresses specified in your formula and see if they are correct. Check if they are not missing any row, column, or colon (:) for ranges with more than one cell.

In the screenshot above, notice that the end row is missing for the first set of ranges of SUMIFS function. (it should be F1:F4).

If you have used named ranges in your formula, check if they still exist in the list of named ranges. Check also their spelling. 

One quick way to do this is to select the cell with the formula and click on the formula bar.

Notice that the ranges in the formula bar have colored fonts.

Invalid named ranges will have a black font color (see “Items”).

To edit the list of named ranges:

1. Go to Formulas >> Name Manager

2. The Name Manager menu will appear. There you’ll see the list of all named ranges.

3. See if the named range in your formula exists in the list.

If you see it, close the Name Manager and correct the spelling in your formula.

If the desired Named Range doesn’t exist, add it. Or, adjust your formula to point it to the equivalent range address.

3. If the text argument in the formula is invalid

In adding text arguments to your formula, remember to enclose texts with double quotation marks (“).

Because if not, Excel will treat them as named ranges. And if it does not have a matching named range, you’ll have the #NAME error.

Conclusion

The #NAME error mainly happens if it sees a text in your formula that it does not recognize. To fix this, find the text that caused the error. Once that is covered, apply the appropriate solution.