Fix #NAME? Error in Excel — Typo in Function Name or Missing Quotes in Formula

The Error

You type a formula and instead of a result, the cell shows #NAME?. Excel doesn't recognize something in your formula — usually a function name it can't find, a text string without quotes, or a named range that doesn't exist.

=SUMF(A1:A10)        → #NAME?
=VLOOKUP(A1,B:C,2)   → works fine
=VLOOKUP(A1,B:C,2,0) → works fine
=COUNTIF(A1:A10,yes) → #NAME?  ← "yes" needs quotes

Root Cause

Excel throws #NAME? when it encounters a token in your formula that it can't resolve. The main culprits:

Typo in function name — =SUMIF works, =SUMF or =SumIF (not a typo issue — Excel is case-insensitive for functions, but SUMF is just wrong)
Text value without double quotes — =COUNTIF(A:A,yes) where yes should be "yes"
Named range that doesn't exist — referenced a name that was deleted or never defined
Missing colon in range reference — =SUM(A1A10) instead of =SUM(A1:A10)
New function not available in your Excel version — e.g., XLOOKUP, LET, SEQUENCE on Excel 2016
Add-in function used without the add-in loaded — Analysis ToolPak functions like NETWORKDAYS if the add-in is off (older Excel)
Curly quotes instead of straight quotes — pasting from Word or a web page can replace " with “”

Fix It

1. Check the Function Name Spelling

Start typing the function name — Excel's autocomplete will show suggestions. If nothing appears, the function name is wrong.

Wrong:  =AVERGEIF(A1:A10,">0")
Right:  =AVERAGEIF(A1:A10,">0")

Wrong:  =CONCATINATE(A1,B1)
Right:  =CONCATENATE(A1,B1)
        -- or in M365: =CONCAT(A1,B1)

The formula bar will underline the unrecognized part in red when you click into the cell — that's your target.

2. Wrap Text Values in Double Quotes

Any literal text in a formula must be in straight double quotes "".

Wrong:  =IF(A1=yes,"OK","No")
Right:  =IF(A1="yes","OK","No")

Wrong:  =COUNTIF(B:B,complete)
Right:  =COUNTIF(B:B,"complete")

Wrong:  =VLOOKUP(A1,Sheet2!B:D,2,false)
Right:  =VLOOKUP(A1,Sheet2!B:D,2,FALSE)  ← FALSE is a keyword, not text
        -- or: =VLOOKUP(A1,Sheet2!B:D,2,0)

Note: TRUE and FALSE are Excel keywords — no quotes needed. Numbers also need no quotes.

3. Fix Curly/Smart Quotes

If you copied a formula from a website or Word doc, the quotes may be typographic (“”) instead of straight (""). Excel won't accept them.

Bad (curly):   =IF(A1="done",1,0)   ← “ and ” characters
Good (straight): =IF(A1="done",1,0)  ← standard " characters

Fix: delete the formula and retype it directly in Excel, or find-and-replace curly quotes with straight ones.

4. Check or Redefine Named Ranges

If your formula references a name like =SUM(SalesData) and SalesData was deleted or renamed:

Go to Formulas → Name Manager (Ctrl+F3)
Check if the name exists and points to the correct range
If missing, click New to recreate it
If the name has an error (shows #REF!), delete and redefine it

-- To define a named range via formula:
-- Select range A1:A100, then Formulas → Define Name → "SalesData"
-- Now =SUM(SalesData) works

5. Fix Missing Colon in Range

Wrong:  =SUM(A1A10)
Right:  =SUM(A1:A10)

Wrong:  =AVERAGE(B2B50)
Right:  =AVERAGE(B2:B50)

6. Use a Compatible Function or Enable the Add-in

If XLOOKUP, LET, UNIQUE, SEQUENCE, or FILTER gives #NAME?, your Excel version doesn't support these dynamic array functions.

XLOOKUP → replace with VLOOKUP or INDEX/MATCH
LET → restructure formula without variable assignment
UNIQUE/SEQUENCE/FILTER → no direct replacement in older Excel; consider upgrading to Microsoft 365

For Analysis ToolPak functions (older Excel versions): File → Options → Add-ins → Excel Add-ins → Go → check Analysis ToolPak → OK

7. Check for Ampersand vs. Plus in Text Concatenation

Wrong:  ="Hello "+A1    ← + operator doesn't concatenate text in Excel
Right:  ="Hello "&A1

This usually gives #VALUE!, but in some edge cases triggers #NAME? if Excel parses it ambiguously.

Quick Diagnosis Trick

Click the cell with #NAME?, then press F2 to enter edit mode. Excel color-codes each part of the formula. Any part that stays plain/uncolored (while ranges turn blue/green/purple) is the unrecognized token — that's where to focus.

Also, click the warning triangle that appears next to the cell → "Help on this error" sometimes pinpoints the exact issue.

Verification

After applying a fix:

Press Enter — the cell should show a numeric or text result instead of #NAME?
If the formula spans multiple cells (array formula or spill range), check that all cells in the range resolved correctly
Use Formulas → Error Checking to scan the whole sheet for remaining errors

Prevention

Always type function names directly in Excel — never copy from external sources without re-typing
Use the Insert Function dialog (Shift+F3) to browse and insert functions with guided argument entry
When upgrading or sharing workbooks, check which Excel version the recipient uses — dynamic array functions (M365) won't work in Excel 2016/2019
After defining named ranges, document them in a dedicated sheet to avoid accidental deletion