#NAME? Error in Excel: Causes, Fixes, and Prevention Tips

Maybe you have typed a formula into Excel and seen the #NAME? error pop-up. This error means Excel doesn’t recognize something you’ve typed. Maybe there’s a small typo, a name’s missing, or a formula is written in a way Excel doesn’t understand. Whatever the cause, it can be a headache if you don’t fix it.

In this article, we’ll go through the causes of the #NAME? Error and explore ways to fix it. 

What Is the #NAME? Error in Excel?

The #NAME? error occurs when Excel encounters something in a formula that it cannot interpret. Formulas follow a strict syntax, and each element—whether a function name, named range, or text string—must be something Excel recognizes. When a component is undefined, misspelled, or improperly formatted, Excel raises the #NAME? error. It's not simply a typo warning that you can ignore. It's a signal that part of the formula is actually unresolvable within Excel's formula language.

Common Causes of the #NAME? Error

Here are a few reasons why you may see #NAME? error in your spreadsheet. 

Misspelled function names

One of the common causes is a typo. One wrong letter can throw an error. For example, if we write CONTIF() instead of COUNTIF(), Excel won’t recognize it and will throw the #NAME? error. 

Misspelled function names cause #NAME? error. Image by Author.

Using new functions in older Excel versions

Sometimes, the error happens because you’re using a function that’s only available in newer Excel versions, like Excel 365. For example, FILTER(), XLOOKUP(), and UNIQUE() won’t work in Excel 2019 or earlier, and that causes the #NAME? error.

Note: If you want to check which version of Excel you’re using, go to File > Account > About Excel to check.

Use of new function in older version causes #NAME? error. Image by Author.

Invalid or misspelled named ranges

Named ranges are custom names we give to groups of cells. So, if we misspell the name or it no longer exists, Excel shows the #NAME? error.

Note: Go to the Formulas tab and open Name Manager to confirm the range name.

Named ranges can apply to only one sheet (local scope) or the whole workbook (global scope). If you’ve fixed the spelling but still see the error, it might be because you’re using a local scope name in the wrong place. Make sure you’re using the right scope.

Typo in named range causes #NAME? error. Image by Author.

Incorrect range references

Excel also shows the #NAME? error if we reference a range incorrectly. For example, typing C2C11 instead of C2:C11, or using a column like CA:C11 that doesn’t exist.

A good way to avoid such typos is to use your mouse to select the range (drag your cursor til the range you want to add).

Invalid range reference causes #NAME? error.  Image by Author.

Missing quotation marks around text

If you use text in a formula, wrap it in straight double quotes like "Apples". Single quotes won’t work. And if you leave out the quotes, Excel thinks it’s a function or named range. And if it can’t find a match, you get the #NAME? error.

For example:

• This works: =IF(A1="Apple", "Yes", "No")

• This won’t: =IF(A1=Apple, Yes, No)

Note: Watch out for smart quotes (like “ or ”) that you might copy from websites or Word. They won’t work in Excel.

Missing double quotes around text causes #NAME? error. Image by Author.

Missing add-ins or custom functions

Some functions need add-ins or custom code to work. For example, EUROCONVERT() only works if the Euro Currency Tools add-in is turned on. If it’s not installed or enabled, you’ll get the #NAME? error.

To check your add-ins: Go to File > Options > Add-ins. You can see what’s active and manage what’s missing.

Disabled add-ins causes #NAME? error. Image by Author.

The same thing happens if you're using a custom function built with VBA.

Let’s say you created a custom function called GetInitials, which takes a full name like John Smith and returns JS. If that function were written in one workbook, it would only work in that file (or in any workbook where the code is available).

So if you try to use =GetInitials(A1) in a different workbook where the code isn’t present, Excel won’t recognize it and will throw the #NAME? error. 

Missing custom function causes #NAME? error. Image by Author.

How to Fix the #NAME? Error in Excel

Now that we know what causes the #NAME? error, let’s look at how to fix it and how to avoid it in the future.

How to fix a single #NAME? Error

#NAME? error is small and easy to miss. Here are some ways to spot and fix it:

• Use autocomplete to avoid typos: As you start typing a formula, Excel suggests functions and named ranges. Press Tab to select the one you want. That way, you avoid typing errors.

To avoid errors, use auto suggestion. Image by Author.

• Check named ranges with Name Manager: If your formula uses a named range, double-check it in Name Manager. Not sure of the name? Go to the Formulas tab, open Name Manager, and make sure the name exists and is spelled correctly.

Check named ranges in Name Manager to avoid typos. Image by Author.

• Fix quote issues in text: When you use text in a formula, wrap it in straight double quotes like "Apple". Curly quotes (“ ”) copied from other apps won’t work. If you’ve copied the formula from a website or MS Word, replace the quotes manually.

• Use your mouse for range references: Instead of typing cell references, use your mouse to select them. It’s quicker and avoids typos like C2C11 instead of C2:C11.

Using mouse to select the range. Image by Author.

• Make sure your Excel version supports the function: Some formulas only work in newer Excel versions like Excel 365. If a function name doesn’t show in the dropdown, it’s probably not supported in your version. To check your version: Go to File > Account > About Excel.

• Enable any missing add-ins: Some functions rely on add-ins to work. To check yours: Go to File > Options > Add-ins. This will show you what’s installed and can turn on anything you need.

• Refresh your formulas: Sometimes, a formula needs a nudge. Press F9 to refresh and force Excel to recalculate everything.

How to find and fix all #NAME? errors in a workbook

If you’ve more than one #NAME? error, here are two easy ways to find and fix them all: 

Use Go To Special to highlight all formula errors

This method shows every error in your sheet, including #NAME?, #DIV/0!, and more. Here’s how you can activate it:

1. Select the range you want to check. 

2. Go to Home tab > Find & Select > Go To Special. Or press F5 and select Special

3. Now, select Formulas and only tick the Error box. 

Excel will now only highlight all the cells with errors. From there, you can check which ones are #NAME? and fix them one by one.

Highlight errors with Go To Special tool. Image by Author.

Use Find and Replace

Another way is to find and replace the error with the Excel built-in Find and Replace tool:

1. In the Home tab > Find & Select > Find. Or press Ctrl + F.

2. Type #NAME? In the Find what box.

3. Click Options and make sure Look in is set to Values.

4. Click Find All to see every match.

You’ll now see every cell with a #NAME? error. Click through them to fix each one or remove them all at once if that’s easier.

Find the #NAME? Error with the Find and Replace tool. Image by Author.

Note: To replace it with something else, press Ctrl + H and go to the Replace tab. Enter #NAME? in the Find what box, and type your replacement in the Replace with box.

How to Prevent #NAME? Errors

The best way to fix #NAME? errors is to stop them before they happen. And you can do this by writing error-free formulas using the Formula Wizard. If you’re unsure how to write a formula, the Formula Wizard can walk you through it. Here’s how:

1. Click the cell you want to use
2. Press the fx button next to the formula bar
3. Pick a function or search for one, then press OK
4. Fill in the input boxes with Excel’s helpful hints
5. Press OK, and your formula appears

Use the Formula Wizard to enter the formula. Image by Author.

Additional Tips and Best Practices

A few good habits can save you from running into the #NAME error again:

Don’t hide the error: fix it

Functions like IFERROR() can be helpful, but they don’t solve the problem. We can use them to cover up errors, but if you don’t fix the root cause, the issue is still there, only hidden. So, fix it first. Then use IFERROR() to smooth out your spreadsheet.

Keep an eye on named ranges

If you're working in a large or shared spreadsheet, check your named ranges regularly. Why? Because over time, things can get renamed, deleted, or changed if lots of people are editing the file. So, a quick check in Name Manager can save you from confusing errors later on.

Final Thoughts 

And that’s the #NAME? error in Excel. It might seem confusing at first, but it usually comes down to something simple, like a typo, a missing quote, or a broken reference. Once you know what to check, it’s easy to fix.

To avoid these errors, use Excel’s autocomplete, double-check your named ranges, and make sure the function works in your version of Excel. Don’t just hide the error with IFERROR(), fix its root cause.

If you want to explore more, check out our #SPILL! Error Excel guide and our Excel Fundamentals skill track.