Learning how to fix the #NAME? error in Excel usually takes less than a minute once the real cause is clear. Excel shows this error when it reads part of a formula as a name it cannot recognize, such as a misspelled function, a missing named range, or text without quotation marks. The steps below work through the causes from most common to least common.
Key Takeaways
- The #NAME? error means Excel does not recognize a word inside the formula.
- Misspelled function names, missing colons in ranges, and unquoted text are the most frequent triggers.
- Deleted or misspelled named ranges can be checked and repaired in Name Manager.
- Newer functions such as XLOOKUP and FILTER return #NAME? in Excel versions that do not support them.
- Pressing F2 and then Enter after a fix forces the cell to recalculate.
How to Fix the #NAME? Error in Excel Step by Step
Locate the Problem Inside the Formula
- Step 1: Click the cell that shows #NAME? and read the formula in the formula bar. The broken part is nearly always a single word or reference, so look for anything that appears different from the rest.
- Step 2: Click the small warning icon that appears next to the cell, then choose Show Calculation Steps if it is available. Another option is to go to the Formulas tab, select Error Checking in the Formula Auditing group, and review the message for that cell.
Tip: Press Ctrl + ` (the grave accent key, usually above Tab) to switch the whole sheet into formula view. This makes it much easier to spot a typo across many cells at once.
Correct the Most Common Causes
- Step 3: Check the function name for spelling mistakes. A formula such as =SUMM(B2:B10) fails because SUMM is not a real function. Retype the name, and when Excel shows the autocomplete list, press Tab to accept the correct function instead of typing the whole word.
- Step 4: Check every range reference for a missing colon. Typing =SUM(B2B10) makes Excel read B2B10 as a name, which triggers #NAME?. The correct version is =SUM(B2:B10).
- Step 5: Look for text that is not wrapped in quotation marks. In a formula like =IF(A2=Paid,1,0), Excel treats Paid as a name. Place straight double quotation marks around the word Paid so Excel reads it as text. Text copied from a web page or Word sometimes carries curly quotation marks, which Excel does not accept, so retype them directly in the formula bar.
- Step 6: Confirm that every named range in the formula exists. Go to the Formulas tab and select Name Manager (or press Ctrl + F3). If the name is missing, misspelled, or points to a deleted range, select New to recreate it or fix the spelling in the formula. To avoid typos, place the cursor in the formula, then choose Use in Formula on the Formulas tab and pick the name from the list.
Rule Out Version and Add-In Issues
- Step 7: Check whether the function is supported by the installed Excel version. XLOOKUP, FILTER, SORT, UNIQUE, and LET are available in Microsoft 365 and Excel 2021 or later (LET and the dynamic array functions are not in Excel 2019 or older, and XLOOKUP is not either). In an older version, replace the function with an alternative such as INDEX and MATCH, or open the file in a newer version.
- Step 8: Enable the add-in if the function belongs to one. Go to File, then Options, then Add-ins. Set the Manage box to Excel Add-ins, select Go, tick the needed add-in (for example, Analysis ToolPak), and select OK.
Confirm the Fix
- Step 9: Press Enter after editing the formula. If other cells use the same formula, copy the corrected cell and paste it over them, or double-click the fill handle in the bottom-right corner of the cell to fill the formula down.
- Step 10: Scan the sheet for leftover errors. Press Ctrl + G, select Special, choose Formulas, clear every box except Errors, and select OK. Excel highlights every cell that still contains an error.
Tip: Start every formula by typing the first letters of the function and letting autocomplete finish it. A name accepted from the list cannot contain a typo.
Common Problems and Fixes
The Formula Looks Correct but Still Shows #NAME?
This usually points to text without quotation marks, curly quotation marks pasted from another source, or a function that the installed version does not support. Retype the quotation marks by hand and check the function against the Excel version in File, then Account.
The Error Appears After Opening a File From Someone Else
The file was most likely built in a newer version of Excel. Functions that the current version does not recognize show #NAME? and may appear with a prefix such as _xlfn in the formula bar. Open the file in Microsoft 365 or a newer version, or rewrite those formulas with older functions.
A Named Range Worked Before and Now Fails
The name was probably deleted, renamed, or limited to a different worksheet. Open Name Manager from the Formulas tab, check that the name is listed, and check the Scope column. A name scoped to one sheet is not recognized on other sheets.
The Error Remains After Fixing the Formula
The cell may not have recalculated. Click the cell, press F2, and then press Enter. If the whole workbook is stuck, go to the Formulas tab, open Calculation Options, and confirm that Automatic is selected.
An Add-In Function Still Fails After Enabling the Add-In
Close and reopen Excel so the add-in loads fully. If the function still fails, return to File, then Options, then Add-ins, and confirm that the add-in is checked and not listed under Disabled Items.
Frequently Asked Questions
What does the #NAME? error mean in Excel?
It means Excel found text in a formula that it cannot recognize as a function, a named range, a cell reference, or a valid text value. Most of the time the cause is a typo or a missing set of quotation marks.
Why does Excel show #NAME? when the formula looks right?
Common hidden causes include curly quotation marks pasted from another program, text not wrapped in quotation marks, a function not supported by the installed Excel version, or a required add-in that is turned off.
Why does VLOOKUP return #NAME?
The most frequent reasons are a misspelled function name, a lookup value written as text without quotation marks, or a table range that is a named range which no longer exists. Check each part of the formula in the formula bar.
Why does XLOOKUP show #NAME? in my workbook?
XLOOKUP is available only in Microsoft 365 and Excel 2021 or later. Earlier versions do not recognize it, so the cell displays #NAME?. Use INDEX and MATCH instead, or open the file in a supported version.
How can all the #NAME? cells on a sheet be found quickly?
Press Ctrl + G, select Special, choose Formulas, clear every checkbox except Errors, and select OK. Excel selects every cell that contains an error value on the sheet.
Can a deleted named range cause the #NAME? error?
Yes. When a name used in a formula is deleted or renamed, the formula can no longer resolve it and returns #NAME?. Open Name Manager on the Formulas tab to recreate the name or update the formula.
Does IFERROR fix the #NAME? error?
No. IFERROR hides the error by showing a different result, but the underlying problem stays in the formula. Fix the cause first, and add IFERROR afterward only if a clean display is needed.
Why does the error stay after the formula is corrected?
The cell may not have recalculated yet. Select the cell, press F2, then press Enter. Also confirm that Calculation Options on the Formulas tab is set to Automatic.
