#NAME? Error In Excel – How To Fix

The #NAME? error is a common Excel error because it’s easy to make mistakes that can cause the #NAME? error. In most cases, you’ll be able to get rid of the #NAME? error by correcting an incorrect spelling in the formula or its syntax.

What is a #NAME? Error in Excel?

The #NAME? error occurs when you’ve entered an input or formula that Excel doesn’t recognize. One of the most common instances where you’ll encounter a #NAME? error is when you misspell a formula, but there are several other reasons as we’ll discuss in the next section. Once you’ve managed to correct the input or formula, the #NAME? error should disappear.

Why Does it Appear?

There are 7 reasons that can cause the #NAME? error.

1. The first reason is that you’ve misspelled the name of the formula. Since Excel wouldn’t know which formula you’re trying to use, it will tell you that you need to fix the name of the formula with a #NAME? error.

2. The second reason is that you’ve entered a cell range incorrectly. For instance, say you were trying to reference the cell range A2:A10, but instead you’ve entered the range as AB:A10. This will give you a #NAME? error.

3. The third reason is that you’ve forgotten to add a colon between a cell range. For instance, you’ve incorrectly referenced a cell range as A2A10, which will give you a #NAME! error.

4. The fourth reason is that you’ve misspelled a named range. For instance, say you’ve named the range ABC, but you’ve referenced it as ACB.

5. The fifth reason is that you’ve referenced a named range with local scope in a different worksheet than where it’s defined.

6. The sixth reason is that you’ve entered a text string without quotes. Skipping quotes when entering a text string makes Excel think that it’s a formula or a named range. This is why skipping quotes can give you a #NAME? error.

7. The seventh reason is that you’ve used smart quotes for a text string. Excel doesn’t recognize smart quotes, and so it will again think of the text string as a formula or named range, and give you a #NUM? error.

Even though there are so many reasons that can cause the #NAME? error, fixing it usually is a mechanical exercise rather than a logical one. Let’s walk through a few examples and look at how you can solve a #NAME? error.

Examples

1. Fixing #NAME? error when function name or cell range are entered incorrectly

When you misspell a function name, Excel can’t simply guess what you mean and apply that function. Since it doesn’t recognize the function you’ve written, it returns a #NAME? error, but fixing it is just as simple.

For instance, say you’ve tried to sum a cell range using the SUM function like so:

The formula returns a #NAME? error because the formula has been misspelled.

However, simply correcting the formula fixes the error. To make sure you always have the right formula name, use Excel’s suggestions that you’ll see while typing the formula.

But what if instead of the function, you enter an incorrect cell range, like so:

You’ll again see a #NAME? error because the range you’ve referenced doesn’t exist.

Excel will let you know that you’ve misspelled the range with the #NAME? error, and fixing it only requires referencing the correct range.

You can also confuse Excel when you skip inserting a colon in between the cell references when inserting a cell range in a formula. The fix, though, is again the same. Just enter the colon, and you’re set.

2. Fix #NAME? error when referencing named ranges

Misspelling named ranges, just like misspelling any other element, results in a #NAME? error. For instance, say you’ve got a named range called 'attendees' containing the number of attendees from each department. And you use the following formula to sum up the number of attendees.

Since you’ve misspelled the named range, you’ll get a #NAME? error. Fixing it is just about correctly spelling the named range.

However, it’s not just the spelling of a named range that can give you a #NAME? error. Remember, every time Excel processes something that it doesn’t recognize, it returns a #NAME? error. This includes named ranges with local scope referenced in a different worksheet.

The thing is, you can set the scope of a named range by pressing Ctrl + F3. Here, you’ll notice a column ‘Scope’ which tells you the scope of a named range. You’ll be able to reference the named range across all worksheets on your workbook only if the scope is set to ‘Workbook’. Otherwise, referencing the named range in a different worksheet will give you a #NAME? error.

Fixing it requires either changing the scope of the named range from the Name Manager (i.e., the dialog box that opens when you press Ctrl + F3).

3. Fix #NAME? error resulting from quotes used for text strings

If you’ve literally ever entered a text string into an Excel formula, you know that they’re supposed to be enclosed in quotation marks.

Why? Well, when you don’t, it makes Excel think it’s a function or a named range. And if no such function or named range exists, you’ll get a #NAME? error.

For instance, say you’ve entered the following formula into a cell.

The formula gives you a #NAME? error because the first argument in the RIGHT function must be a text string. Unless you put it in quotes, Excel won’t recognize it. The good thing is, you can fix it by simply enclosing the text string in quotes.

However, you might also get an error if you copy the formula from a source like a Word document into a cell in your worksheet. This is because Excel doesn’t recognize smart quotes.

You can usually distinguish between regular and smart quotes by checking whether they’re straight or curved. Regular curves are straight and look like two perpendicular lines without any curves.

That’s all the ways to eliminate the #NAME? error from your worksheet. It’s almost too easy. All you need to do is correct mechanical errors, except for when you’ve referenced a named range with a local scope in a different worksheet. While you get up to speed with solving this error, we’ll have another tutorial ready for you to get your hands dirty with.