#NAME? Error in Excel: Typos, Quotes and Missing Functions

The #NAME? error in Excel means your formula contains a word Excel doesn't recognise. It's almost always a typing problem: a misspelled function, text without quotes, a missing colon in a range, or a function that your version of Excel doesn't have. The good news is that it's quick to fix once you know where to look, and this guide covers each cause.

What does the #NAME? error mean?#

Every word in a formula must be something Excel knows: a function (SUM, IF), a cell or range (A2, B2:B9), a named range you created (TaxRate), or text inside double quotes ("Pass"). Anything else is an unknown name, and Excel shows #NAME?.

Like #REF!, #NAME? means the formula is wrong, not the data. So the fix is always to edit the formula. If formulas are new to you, start with how to write your first Excel formula.

Cause 1: a misspelled function name#

A single wrong letter is enough:

A tip that catches typos: type function names in lowercase, like =sum(b2:b9). When you press Enter, Excel changes the names it recognises to uppercase. If a name stays lowercase, Excel didn't recognise it.

Even better, let Excel spell it. As you type =VL, Formula AutoComplete shows a list of matching functions. Use the arrow keys to pick one and press Tab. Excel inserts the correct name and the opening bracket.

Cause 2: text without double quotes#

Text inside a formula must be in double quotes. Without them, Excel thinks the word is a name.

=IF(B2>=50,Pass,Fail)

Excel looks for something called Pass, finds nothing, and returns #NAME?. The fix:

=IF(B2>=50,"Pass","Fail")

For a score of 72 in B2, the result is Pass. The same rule applies everywhere text appears: in criteria like =COUNTIF(B2:B9,"Paid"), in comparisons like =B2="Leeds" (see the TRUE, FALSE and comparisons lesson), and when joining text with &.

Curly quotes from Word, email or web pages#

If you copy a formula from a document or website, the quotes may be "smart" curly quotes (“Pass”) instead of straight ones ("Pass"). Excel only accepts straight quotes, so the formula breaks. Delete the curly quotes and retype them in Excel's formula bar.

Numbers, TRUE and FALSE don't need quotes. =IF(B2>=50,1,0) is fine.

Cause 3: a range without its colon#

A range needs a colon between the first and last cell. A2A9 isn't a range; it's an unknown name.

The easiest way to avoid this is to select the range with the mouse or with Shift plus the arrow keys while you type the formula. Excel writes the colon for you.

Cause 4: a named range that doesn't exist#

If you write =B2*TaxRate, Excel needs a name called TaxRate. If it was never created, was deleted, or is spelled differently (Tax_Rate), the formula returns #NAME?.

To check, open Formulas > Name Manager (on Windows, also Ctrl+F3). It lists every name in the workbook and the cells it refers to. Create the missing name, or correct the spelling in the formula. You can also pick a name from Formulas > Use in Formula so you never mistype it.

Cause 5: the function isn't in your version of Excel#

New functions show #NAME? in versions of Excel that don't have them. The formula is correct; the software is too old to understand it.

This often appears when a file built in Microsoft 365 is opened in an older version. If the file must work everywhere, use older equivalents: VLOOKUP or INDEX MATCH instead of XLOOKUP, nested IF instead of IFS. Our comparison of Excel 365, 2021 and 2019 lists what each version includes.

Cause 6: a reference to another sheet typed by hand#

References to other sheets can also go wrong when they're typed instead of clicked. A sheet name with spaces must be wrapped in single quotes, like ='Last Year'!B5, and the exclamation mark separates the sheet from the cell. Leave out the !B4 part of =Sales!B4 and Excel reads Sales as a name it doesn't know, so it returns #NAME?.

The fix is the same as for ranges: don't type references to other sheets. Type =, click the other sheet's tab, click the cell and press Enter. Excel writes the sheet name, quotes and exclamation mark correctly every time.

How to find the problem in a long formula#

When the cause isn't obvious:

1. Click the cell and the warning icon next to it, then choose Show Calculation Steps. Evaluate Formula shows which part turns into #NAME?.
2. Click inside the formula bar. Excel colors each cell reference it recognises. A word that should be a range but isn't colored is a likely suspect.
3. Use Insert Function (the fx button next to the formula bar, or Shift+F3 on Windows) to rebuild the function with labelled boxes for each argument.

Don't wrap #NAME? in IFERROR. It would hide the error, but the formula would still be broken. Once the formula runs, check that it gives the answer you expect: a formula without brackets in the right places calculates without any error but can still be wrong, as our guide to Excel's order of operations explains. For a different-looking problem, a cell full of #####, see why Excel shows ##### in a cell.

Practise it#

Fix the IF formula in the exercise on this page. Then go back to basics with the your first formulas lesson to build the habit of typing clean formulas, and the TRUE, FALSE and comparisons lesson to practise text in quotes. If you're wondering how long it takes to get comfortable, see how long it takes to learn Excel for a job.