#NAME? Error - Excel Glossary

The #NAME? error is one of Excel's most common formula errors, appearing when the application fails to interpret text within a formula. This can result from typos in function names (e.g., SUMIF instead of SUMIFS), references to non-existent named ranges, or missing quotation marks for text strings. Unlike #REF! or #VALUE! errors, #NAME? specifically signals a naming or recognition problem. Understanding this error helps maintain data integrity and prevents cascading calculation failures. It appears immediately when the formula is entered, making it relatively easy to diagnose.

Definition

The #NAME? error occurs when Excel cannot recognize a formula's text as valid syntax. This typically happens due to misspelled function names, undefined named ranges, or missing quotation marks around text. It's critical to identify and fix this error quickly to ensure spreadsheet calculations work correctly.

Key Points

1Occurs when Excel doesn't recognize function names, named ranges, or formula syntax.
2Most common causes: misspelled function names, undefined named ranges, or missing text quotation marks.
3Appears immediately after entering the formula; easily preventable with careful syntax checking.

Practical Examples

→=SUM(A1:A10) entered as =SUMM(A1:A10) triggers #NAME? due to the extra 'M'.
→=IF(B5>10, "High", "Low") fails with #NAME? if the quotation marks are omitted around text values.

Detailed Examples

Financial analyst using inventory calculations

A formula =VLOOKUP(SKU, Inventory_Table, 3, FALSE) returns #NAME? because the named range 'Inventory_Table' was never created. The analyst must define the range first via Sheet > Named Ranges before the formula works.

Marketing team tracking campaign metrics

=AVERAGEIF(Campaign_Data, criteria) returns #NAME? when Campaign_Data is misspelled or doesn't exist as a defined range. Using the exact range address or creating the named range resolves the issue immediately.

Best Practices

✓Always spell function names correctly and verify they exist in your Excel version (e.g., SUMIFS vs. SUMIF). Use Excel's function tooltip or autocomplete feature to avoid typos.
✓Create named ranges systematically before using them in formulas; name them descriptively (e.g., 'Q4_Sales' instead of 'Range1') for clarity and maintainability.
✓Wrap text values in quotation marks and use the correct syntax for operators (e.g., =IF(A1="Yes", 1, 0) not =IF(A1=Yes, 1, 0)).

Common Mistakes

✕Misspelling function names: =SUMIF() entered as =SUMIIF() or using regional language function names in English settings. Always check the exact function name in Excel's function list.
✕Forgetting quotation marks for text: =IF(A1=Active, 1, 0) should be =IF(A1="Active", 1, 0). Text without quotes is treated as a variable name.
✕Using undefined named ranges: Referencing a range that was deleted or never created. Always verify named ranges exist via Formulas > Name Manager.

Tips

✓Use Excel's Formula Auditing tools (Formulas > Evaluate Formula) to step through your formula and pinpoint exactly where the error occurs.
✓Enable AutoComplete: Start typing a function and Excel suggests matching names, reducing typo risk significantly.
✓Leverage the Name Manager (Formulas > Name Manager) to quickly check if your named range exists and is spelled correctly.

Related Excel Functions

IFERROR ISNUMBER ISNA ISERROR IF VLOOKUP HLOOKUP SUMIF AVERAGEIF COUNTIF

Frequently Asked Questions

What's the difference between #NAME? and #VALUE! errors?

#NAME? indicates Excel doesn't recognize the formula text (function name, named range, or syntax), while #VALUE! means the formula is recognized but contains incompatible data types. For example, =SUM("text") produces #VALUE!, not #NAME?.

Can I fix #NAME? by simply deleting and re-entering the formula?

Sometimes, especially if caused by a typo. However, if the error stems from a missing named range or function, deleting won't help—you must first create the named range or correct the function name. Always identify the root cause before re-entering.

Why does my formula with a named range show #NAME? even though I created it?

This typically happens when the named range is scoped to a specific sheet but used on another sheet. Ensure the named range has workbook-level scope (not sheet-level), or reference it as SheetName!RangeName. Check your named range definition in Formulas > Name Manager.

Does #NAME? affect other cells in my spreadsheet?

No, #NAME? only appears in the cell with the erroneous formula. However, any formula that references that cell will display #NAME? as well, creating a cascade effect. Fix the source error to resolve all dependent errors.