#NAME? error: what it means and how to fix it
The formula contains a word the spreadsheet doesn't recognise.
#NAME? means the spreadsheet read a word it doesn't know: a misspelled function, a named range that doesn't exist, or text that should have been in double quotes.
Common causes, ranked
- 1
A misspelled function name
Microsoft calls this the top reason:
=SUMM(B2:B9)or=VLOKUP(…).Fix: Fix the spelling. Start typing the name and pick it from the list of suggestions, so it can't be misspelled.
Shows the error
=SUMM(B2:B9)Fixed
=SUM(B2:B9) - 2
Text without double quotes
Overwithout quotes looks like a name, not a word to show.Fix: Put text in double quotes.
Shows the error
=IF(D2>C2,Over,OK)Fixed
=IF(D2>C2,"Over","OK") - 3
A missing colon in a range
B2B9isn't a range; it reads as an unknown name.Fix: Write ranges with a colon:
B2:B9.Shows the error
=SUM(B2B9)Fixed
=SUM(B2:B9) - 4
A named range that doesn't exist (or is misspelled)
The formula uses a name like
TaxRatethat was never defined in the workbook.Fix: Define the name (Excel: Formulas › Name Manager), or fix the spelling.
- 5
A function your version doesn't have
Newer functions such as
XLOOKUP(Excel 2021+) orLAMBDA(Excel 2024+) aren't in older versions, and some functions need an add-in.Fix: Use a function your version has (for example
INDEX/MATCHinstead of XLOOKUP), or enable the add-in.
See it live
Edit the formula in E2 and fix the error yourself.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Type | Budget | Actual | Status |
| 2 | Rent | Fixed | $1,200.00 | $1,200.00 | #NAME? |
| 3 | Groceries | Variable | $450.00 | $512.40 | #NAME? |
| 4 | Utilities | Fixed | $160.00 | $148.75 | #NAME? |
| 5 | Transport | Variable | $120.00 | $96.50 | #NAME? |
| 6 | Phone & internet | Fixed | $65.00 | $65.00 | #NAME? |
| 7 | Eating out | Variable | $150.00 | $203.10 | #NAME? |
| 8 | Subscriptions | Fixed | $38.00 | $44.97 | #NAME? |
| 9 | Gym | Fixed | $40.00 | $40.00 | #NAME? |
| 10 | Gifts | Variable | $60.00 | $25.00 | #NAME? |
| 11 | Savings | Fixed | $300.00 | $300.00 | #NAME? |
Should you hide it?
Don't hide #NAME?. Microsoft says not to mask it with IFERROR: it means the formula is mistyped, so it can never give the right answer.
Careful: A hidden #NAME? is a formula that silently does nothing. Fix the spelling instead.
Excel vs Google Sheets
#NAME? is an error value in both Excel and Google Sheets. Function names differ slightly between them; check the function exists in the app you're using.
Sources
Practise this in 5 minutes
Fixing #NAME? is taught in Lesson 10: TRUE, FALSE and IF (Unit 3, Decisions), on a live sheet with instant feedback. Pro
Open “TRUE, FALSE and IF”Need a quick reference? The formula cheat sheet is free to read and print.