[Excel] Error: #NAME? — How to Fix It

Summary

The #NAME? error occurs when Excel doesn’t recognize text in a formula — typically due to a typo, missing named range, or unavailable function. Common cases include misspelling function names (e.g., =SUMM(A1:A5)), using localized names incorrectly, or referencing add-ins not enabled. Fix it by checking spelling, verifying named ranges, ensuring add-ins are active, and quoting any text values. Works in Excel 2010+, 2016, and Microsoft 365.

Context

Excel parses each formula for valid function names, named ranges, and syntax. When it encounters unknown identifiers, it throws #NAME?. This often happens when formulas are copied from another language version of Excel, when macros or add-ins defining custom functions are disabled, or when text values lack quotation marks. It can also appear when using modern functions (TEXTJOIN, LET, XLOOKUP) in older Excel versions that don’t support them. Identifying the unrecognized element is the first step to correction.

Probable Cause

  • Misspelled function or named range. Any typo in built-in names (=AVARAGE() instead of =AVERAGE()) triggers #NAME?.
  • Regional mismatch. Localized versions of Excel use translated function names (e.g., =SOMA() in Portuguese vs =SUM() in English).
  • Missing add-in or custom function. VBA-defined or COM add-in functions fail if the add-in is not loaded.
  • Unquoted text string. Typing =Hello instead of ="Hello" produces the error.
  • Unsupported function in current version. Modern formulas copied into older Excel builds show #NAME? because the function doesn’t exist.

Quick Fix

  1. Check for spelling and syntax errors. Correct misspelled functions or names.
=SUM(A1:A5)    ✅
=SUMM(A1:A5)   ❌ → #NAME?
  1. Use the Insert Function tool. Press Shift + F3 to open the function list and confirm the correct spelling and arguments.
  2. For localized Excel versions: verify that the function name matches your language setting.
# English
=SUM(A1:A5)
# Portuguese (Excel PT-BR)
=SOMA(A1:A5)
  1. Ensure named ranges exist. Go to Formulas → Name Manager (Ctrl + F3) and recreate or correct missing names.
  2. Verify add-ins and macros. Go to File → Options → Add-ins, enable required add-ins, and reopen the workbook.
  3. Quote text strings. Wrap plain text in quotation marks to avoid accidental name lookup.
=IF(A1="Yes", "Approved", "Pending")

Full Example

Scenario 1: Typo in function name.

=SUMM(A1:A5)
→ #NAME?

Fix:

=SUM(A1:A5)

Scenario 2: Formula copied from English Excel into a localized version.

=TEXTJOIN(", ", TRUE, A1:A3)
→ #NAME?

Fix: Replace with local equivalent or use function translator add-in.

=UNIRTEXTO(", ", VERDADEIRO, A1:A3)

Scenario 3: Missing named range.

=SUM(Sales_Q1)
→ #NAME? if "Sales_Q1" was deleted

Fix: Recreate named range via Formulas → Name Manager → New → Define "Sales_Q1".

Scenario 4: Unrecognized custom function.

=MyMacroFunction(A1)
→ #NAME? if VBA project disabled

Fix: Enable macros and verify add-ins under Developer → Add-ins.

Pitfalls & Debug

  • Symptom → Function not found in list. Fix → Check Excel version; some functions exist only in newer builds (e.g., TEXTJOIN, XLOOKUP).
  • Symptom → Formula copied from different locale. Fix → Use Microsoft’s Function Translator add-in to convert names automatically.
  • Symptom → Named range removed. Fix → Recreate via Name Manager and update formulas.
  • Symptom → Custom VBA function fails. Fix → Ensure macro security allows execution and the module defining the function is present.
  • Symptom → Missing add-in. Fix → Enable in File → Options → Add-ins → Manage COM/VBA Add-ins.

Validation & Next Steps

After correction, verify formula validity using Excel’s auditing tools:

Formulas → Error Checking → Check for Errors
Formulas → Evaluate Formula

Confirm the function name appears in IntelliSense suggestions when typing. For workbooks shared across versions or regions, prefer portable formulas (e.g., INDEX/MATCH over newer XLOOKUP) or include compatibility notes. Recreate missing names and keep add-ins consistent between collaborators.

Sources

https://support.microsoft.com/en-us/office/how-to-correct-a-name-error

https://support.microsoft.com/en-us/office/use-the-function-translator-add-in

https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas

Labels

Tool/Excel, OS/Cross-platform, Topic/Functions & Named Ranges