Unlike #REF! or #DIV/0!, the #NAME? error is almost always a syntax or typing problem — not a data problem. That means it is almost always fixable in seconds once you know what to look for. The key is identifying which of the seven causes applies to your formula.
Misspelled Function Name
The most common cause. If you type =SUMM(A1:A10) instead of =SUM(A1:A10), Excel cannot recognise "SUMM" as a function — so it returns #NAME?. Excel function names are not case-sensitive, but spelling matters exactly. Fix: click the cell, look at the formula bar, and correct the spelling. Excel's autocomplete will suggest valid function names as you type — use it.
Using a Named Range That Does Not Exist
If your formula references a named range — like =SUM(Revenue) — but "Revenue" has not been defined (or was deleted), Excel shows #NAME?. Fix: go to Formulas → Name Manager to see all defined names. If the name is missing, create it: Formulas → Define Name. If it was deleted accidentally, recreate it pointing to the correct cell range.
Text String Missing Quotation Marks
Text values in formulas must be wrapped in double quotes. If you write =IF(A1=Yes, "OK", "No") instead of =IF(A1="Yes", "OK", "No"), Excel tries to interpret "Yes" as a named range — and when it cannot find it, returns #NAME?. Fix: always wrap text criteria in double quotes: =IF(A1="Yes", "OK", "No").
Missing Colon in a Range Reference
A range like A1A10 (missing the colon) is not a valid reference. Excel may interpret it as a name lookup and return #NAME? when it cannot find a defined name matching that string. Fix: check your range references and ensure every range uses a colon: A1:A10.
Using a Function From a Different Version of Excel
Some functions — like XLOOKUP, UNIQUE, FILTER, SEQUENCE — were introduced in Excel 2019 or Microsoft 365. If you open a file in an older version of Excel (2016 or earlier), these functions are not recognised and show #NAME?. Fix: use a backwards-compatible alternative (VLOOKUP instead of XLOOKUP, for example) or ensure all users of the file have a compatible Excel version.
Uninstalled or Disabled Add-In Function
Functions provided by Excel add-ins (like Analysis ToolPak functions such as NETWORKDAYS.INTL in older versions) return #NAME? if the add-in is not loaded. Fix: go to File → Options → Add-ins → Manage: Excel Add-ins → Go, and enable the required add-in.
Space Instead of Colon in a Named Range or Intersection
A space between two range references is Excel's intersection operator — it returns the cell(s) where two ranges overlap. If your ranges do not intersect, Excel returns #NAME? (in some versions, #NULL!). This is rarely intentional. Fix: if you meant to reference two ranges, use a comma to separate them: =SUM(A1:A5, C1:C5). If you meant a single range, use a colon.
How to Find All #NAME? Errors in Your Workbook
Press Ctrl+F (Find) and search for #NAME?. Change the "Look in" dropdown to "Values" and click "Find All" — Excel lists every cell containing this error. Alternatively, press F5 → Special → Formulas → Errors to select all error cells at once on the current sheet.
To find #NAME? errors across all sheets at once, use the ExcelErrorFinder audit tool — it scans every cell in every sheet and lists every error by address and formula.
Quick Diagnostic: What Does Your Formula Show?
- →Formula has a word with no quotes? → Text string missing double quotes (cause 3)
- →Formula references a name not in Name Manager? → Undefined named range (cause 2)
- →Function name looks slightly off? → Misspelled function (cause 1)
- →File was created in newer Excel? → Unsupported function in your version (cause 5)
- →Range reference missing colon? → Syntax error in range (cause 4)
Frequently Asked Questions
Does #NAME? affect other cells?
Yes. Any formula that references a cell showing #NAME? will also return an error (usually #NAME? or #VALUE!). Fix the root cause first, then the dependent cells will update automatically.
Can #NAME? appear even if I didn't change anything?
Yes — if someone deleted a named range, uninstalled an add-in, or the file was moved from a newer version of Excel to an older one. The formula didn't change, but its environment did.
Why does Excel sometimes suggest fixing #NAME? errors automatically?
Excel's error checking feature (the green triangle in the cell corner) can detect misspelled function names. Click the triangle and look for a "Did you mean..." suggestion — Excel will often offer to correct the spelling in one click.