formulastreak

#NAME? error: what it means and how to fix it

#NAME?

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. 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. 2

    Text without double quotes

    Over without 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. 3

    A missing colon in a range

    B2B9 isn'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. 4

    A named range that doesn't exist (or is misspelled)

    The formula uses a name like TaxRate that was never defined in the workbook.

    Fix: Define the name (Excel: Formulas › Name Manager), or fix the spelling.

  5. 5

    A function your version doesn't have

    Newer functions such as XLOOKUP (Excel 2021+) or LAMBDA (Excel 2024+) aren't in older versions, and some functions need an add-in.

    Fix: Use a function your version has (for example INDEX/MATCH instead of XLOOKUP), or enable the add-in.

See it live

Edit the formula in E2 and fix the error yourself.

Over and OK have no quotes, so the formula shows #NAME? · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = #NAME?
ABCDE
1CategoryTypeBudgetActualStatus
2RentFixed$1,200.00$1,200.00#NAME?
3GroceriesVariable$450.00$512.40#NAME?
4UtilitiesFixed$160.00$148.75#NAME?
5TransportVariable$120.00$96.50#NAME?
6Phone & internetFixed$65.00$65.00#NAME?
7Eating outVariable$150.00$203.10#NAME?
8SubscriptionsFixed$38.00$44.97#NAME?
9GymFixed$40.00$40.00#NAME?
10GiftsVariable$60.00$25.00#NAME?
11SavingsFixed$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”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.