How to Fix the #NAME? Error in Microsoft Excel Formulas

The #NAME? error in Microsoft Excel appears when the spreadsheet formula calculation engine encounters text inside a formula string that it does not recognize as a valid function name, a defined named range, a sheet reference, or a cell coordinate. Unlike the #REF! error in Excel (which indicates a missing coordinate address), the #NAME? error indicates a syntax or typographical mistake—such as typing =VLOOKP() instead of =VLOOKUP(), omitting quotation marks around text strings, or referencing a deleted Named Range. Resolving this issue involves verifying formula spelling, inspecting quotation marks, and auditing the Name Manager.

Why Excel Displays the #NAME? Error

When you type an equals sign (=) followed by characters, Excel evaluates the syntax sequentially: it checks whether the term is a built-in function name, an add-in function, a user-defined LAMBDA/VBA function, a valid column/row cell reference (like B5), or a user-created Named Range. If the token matches none of these defined structures, Excel assumes it is an invalid name and flags the cell with #NAME?.

The primary origins of the error include:

• Misspelled Function Names: Typographical errors in standard Excel functions (e.g., =SUMM(A1:A10), =IFF(...), or =XLOOKP(...)).
• Missing Quotation Marks Around Text Strings: Entering text strings in arguments without surrounding them in double quotes (e.g., =IF(A1=Yes, 1, 0) instead of =IF(A1="Yes", 1, 0)). Excel tries to interpret Yes as a named range.
• Missing Colons in Range References: Omitting the colon between range boundary coordinates (e.g., =SUM(A1 A10) instead of =SUM(A1:A10)).
• Undefined or Scoped Named Ranges: Referencing a Named Range that was either deleted, misspelled, or scoped strictly to a different worksheet tab. (Compare with fixing the #VALUE! error in Excel).

Incorrect Formula	Identified Mistake	Corrected Syntax
=VLOOKP(A2, D:E, 2, 0)	Misspelled built-in function name	=VLOOKUP(A2, D:E, 2, 0)
=IF(B2=Complete, 100, 0)	Missing quotation marks around text argument	=IF(B2="Complete", 100, 0)
=AVERAGE(B2 B20)	Missing colon operator in cell range	=AVERAGE(B2:B20)
=Total_Tax * 0.20	Unregistered or misspelled Named Range	Verify range spelling in Name Manager

Step 1: Check and Correct Function Spelling via Formula AutoComplete

The most common cause of #NAME? is a simple typo in the function name. Using Excel’s built-in Formula AutoComplete prevents typing mistakes:

1. Click on the cell displaying #NAME? and press F2 to enter edit mode.
2. Examine the function name directly following the equals sign.
3. If you mistyped the name (such as =COUNTAA() or =CONCATINATE()), backspace to the first few letters.
4. A dropdown list of valid built-in Excel functions matching your typing will appear automatically.
5. Use the arrow keys to highlight the correct function name and press the Tab key. Excel will insert the complete function name and opening bracket automatically.

Step 2: Wrap All Literal Text Arguments in Double Quotation Marks

In Excel formulas, any text string entered without quotation marks is parsed as a reference to a variable or Named Range. If no matching range exists, Excel returns #NAME?.

1. Select the cell containing the error and examine the formula bar.
2. Check logical comparison formulas (such as IF, IFS, XLOOKUP, or FILTER).
3. Look for unquoted words. For example:

=IF(A2=Approved, "Process", "Hold")

4. In this formula, Excel searches for a Named Range named Approved. Because none exists, it displays #NAME?.
5. Add double quotation marks around the text literal:

=IF(A2="Approved", "Process", "Hold")

6. Press Enter to recalculate the formula.

Step 3: Verify Colon Syntax in Range References

If you type a range reference without a separating colon operator, Excel interprets the space-separated letters as an invalid name.

1. Inspect your range coordinates inside statistical or mathematical formulas (like SUM, MAX, or MIN).
2. Ensure that range parameters contain a colon:

=SUM(C5:C25)

3. If the colon was omitted (e.g., =SUM(C5 C25)), Excel attempts to perform an intersection operation between two unrecognised tokens, yielding #NAME?.

Step 4: Audit Defined Names in the Name Manager

If your workbook utilizes Named Ranges (such as Tax_Rate or Q1_Revenue), verify that the name is spelled identically and scoped correctly.

1. Click the Formulas tab on the Excel ribbon menu.
2. Click the Name Manager button (or press Ctrl + F3).
3. Review the list of defined names:
   • Verify that the name in your formula exists in the list and is spelled identically.
   • Check the Scope column: If a name is scoped strictly to Sheet1, referencing that name on Sheet2 will return #NAME? unless prefixed with the sheet tab name (e.g., Sheet1!Tax_Rate).
4. If the Named Range was accidentally deleted, click New…, assign the name, and select the appropriate cell range in the Refers to box.

Step 5: Use the Function Wizard to Construct Complex Formulas

If you are unfamiliar with a function’s argument requirements, using the graphical Function Wizard eliminates syntax errors completely.

1. Select the cell where you want the calculation.
2. Click the Insert Function (fx) button located directly to the left of the formula bar.
3. Search for the function by name and click OK.
4. Fill in the fields inside the Function Arguments dialogue box. Excel automatically validates data types, inserts quotation marks around text strings where required, and displays the evaluated result in real time before you commit the formula.