#NAME? means Excel does not recognise something in your formula. There are four usual causes: a misspelt function or named range, text typed without quotes, a function your version of Excel does not have, and a name that was deleted. Each has a quick test, listed below.
Cause 1: a misspelt function or named range
=SUMFI(...) instead of =SUMIFS(...), or a range named Food_Spend that you typed as FoodSpend. Test: click the cell, press F2 and look at the formula. Type the function name again slowly in a blank cell; Excel's suggestion list appears as you type. If the function you want never shows in the list, it is either misspelt or not in your version. For names, press Ctrl+F3 to open the Name Manager and compare the spelling.
Cause 2: text without quotes
In =IF(C2=Food,B2,0) Excel reads Food as a name, not a word, and returns #NAME?. Text must sit in double quotes: =IF(C2="Food",B2,0). The same applies inside SUMIFS: =SUMIFS($B$2:$B$500,$C$2:$C$500,"Food") works, while writing Food on its own does not. Test: look for any word in the formula that is not a function, a cell address or a name, and wrap it in straight quotes. Pasting from WhatsApp or Word can bring curly quotes that Excel does not accept; retype them.
Cause 3: a function your Excel version does not have
XLOOKUP, FILTER, UNIQUE, SORT, SEQUENCE and LET need Excel 2021 or Microsoft 365. A file that uses =XLOOKUP(A2,$H$2:$H$40,$I$2:$I$40,"not found") opens fine in Microsoft 365, but a colleague on Excel 2019 sees #NAME? in every cell that uses it. Test: ask which version the other person has, or try your formula in a different copy of Excel.
| Need | Newer function | Works in older versions |
|---|---|---|
| Look up a rate or category | =XLOOKUP(A2,$H$2:$H$40,$I$2:$I$40,"not found") | =IFERROR(INDEX($I$2:$I$40,MATCH(A2,$H$2:$H$40,0)),"not found") |
| Sorted list of categories | =SORT($C$2:$C$500) | Data tab, Sort, on a copy of the column |
| List of unique categories | =UNIQUE($C$2:$C$500) | Remove Duplicates on a copy of the column |
When you are sharing the file, either use formulas that work in older versions or save the result as values before sending.
Cause 4: a deleted or never-created name
If you wrote =SUM(Food_Spend) and then deleted the name, every formula that used it shows #NAME?. Test: open the Name Manager with Ctrl+F3. If the name is missing, recreate it by selecting the range and typing the name in the Name Box to the left of the formula bar. If your name only exists in a different workbook, the formula will not find it either.
Does Google Sheets give the same error?
Yes, with the same causes. A misspelt function shows #NAME?, and the hover message names the function it did not recognise. Version problems are rarer, because Sheets updates for everyone, but a custom function written in Apps Script shows #NAME? in a copy of the file that does not contain the script.
A faster way to hunt for it
- Press Ctrl+F, set Look in to Values and search for #NAME? to land on the cells showing the error.
- Check whether one cell works and its neighbour does not; compare their formulas character by character.
- Turn on Show Formulas under the Formulas tab so you can read the whole sheet.
- Copy a working formula over a failing one and edit one thing at a time.
Quick checklist
- Is every function name spelt correctly and does it autocomplete?
- Is every piece of text in straight double quotes?
- Does the other person's Excel version have the function?
- Are all named ranges present in the Name Manager?
- Is there a colon in every range?
If you are assembling categories from raw statement lines, SpendixAI can categorise them with your own rules first, which spares you a big category lookup formula in the sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


