How to Fix #NAME? Error in Excel (Step-by-Step)

Why is Excel showing #NAME? when the formula looks exactly right? That's the specific frustration behind the name error in Excel, and it trips up people who've been using spreadsheets for years, not just beginners. The formula looks fine. The function name seems correct. And yet Excel refuses to run it.

Before you start editing cells one by one, there's a smarter way to handle this. This guide works through it as a diagnostic process: find all the broken cells first, then fix each cause at its root. That approach takes maybe ten minutes on a large workbook versus an hour of hunting blind.

If you're newer to Excel errors in general, the common Excel errors and troubleshooting guide gives helpful context on how these error types relate to each other.

---

What You'll Fix — and the One Thing to Check Before You Touch Any #NAME? Error in Excel

By the end of this guide, you'll have a working formula — or formulas, if you're dealing with a shared workbook full of them. No more red cells, no more broken outputs.

One quick prerequisite: make sure formula autocomplete is turned on. Go to File → Options → Formulas and confirm that Formula AutoComplete is checked. This feature suggests function names as you type, and it's your first line of defense against the #NAME? error before it ever happens. If autocomplete is off, you're writing formulas blind.

Here's what a name error in Excel actually means: Excel hit something in your formula it doesn't recognize — a function name, a text string, a named range — and it stopped. It won't guess. It won't try the closest match. It returns #NAME? and waits for you to fix it. This isn't a calculation problem. It's a recognition problem. Excel is telling you it doesn't know what that thing is.

Excel is not being picky — it's being literal. There's a difference.

---

Step 1: Find Every #NAME? Error Across Your Workbook Before You Fix a Single Cell

This is the step most people skip, and it's why they end up fixing the same error type four times in four different cells instead of all at once. Here's how to audit your entire workbook in about sixty seconds:

1. Press Ctrl+G (Windows) or Cmd+G (Mac) to open the Go To dialog, then click Special.
2. Select Formulas, uncheck everything except Errors, and click OK. Excel will select every error cell on the active sheet.
3. To find errors across all sheets, use Find and Replace instead. Press Ctrl+H, type #NAME? in the Find field, leave Replace blank, and click Find All. The results pane at the bottom lists every matching cell with sheet names included. Click any result to jump directly to that cell.

Write down how many #NAME? errors you have and on which sheets. That gives you a complete map before you change anything. It also tells you whether you're dealing with one bad formula that got copy-pasted fifty times — which changes your fix strategy significantly.

---

Step 2: Fix the Real Causes of the Excel NAME Error One by One

Now that you know where all the broken cells are, work through the actual causes. These are ordered by how often they come up — this sequence gets most people to the answer fast.

Misspelled Function Names and the Autocomplete Shortcut

A typo in the function name is the most common cause of the name error in Excel. =SUMIF typed as =SUMIIF, or =VLOKUP instead of =VLOOKUP: Excel doesn't recognize it, so #NAME? is what you get.

Click into the broken cell and check the formula bar. If you see a function name with a red wavy underline, that's your culprit. Delete the function name and start retyping it. When autocomplete shows the correct suggestion, press Tab to confirm it — don't just keep typing. Pressing Tab is how Excel locks in the recognized function name. Pressing Enter skips that confirmation step.

Build formulas from the inside out: test the innermost function first, confirm it works, then wrap the next layer around it. Catching a misspelled function name at step one is far less painful than finding it buried inside a nested formula at step four.

Curly Quotes from Copy-Pasted Formulas

This one catches people badly, and it's almost invisible. When you copy a formula from a website, a Word document, or a PDF, the quote marks often come through as curly (smart) quotes — "like these" — instead of the straight quotes Excel requires: "like these".

Excel doesn't recognize curly quotes as text string delimiters, so the formula breaks with #NAME? even though it looks completely correct at a glance.

To check: click into the formula cell, look at the formula bar, and zoom in on any quote marks around text strings. If they curve, they're wrong. Delete and retype them manually using your keyboard — don't paste. This is one of those cases where "I followed the tutorial exactly" still produces an error, which is exactly why you should always test externally sourced formulas before using them anywhere that matters.

If your formula still shows a name error after checking spelling and named ranges, copy the formula into Notepad, delete it from Excel entirely, and retype it manually. That forces every character to be clean.

Broken Named Ranges and the Name Manager Fix

If your formula references a named range — something like =SUM(QuarterlySales) — and that name has been deleted, renamed, or never defined at all, Excel throws #NAME? because it can't find what you're pointing at.

Go to Formulas → Name Manager (or press Ctrl+F3). You'll see every named range in the workbook. If the name your formula uses isn't listed, you've found your problem. Either recreate the named range from the correct cell selection, or update the formula to reference the current name directly.

This is a common issue in inherited workbooks: someone renamed a range to clean up the Name Manager without realizing three formulas depended on the old name.

The _xlfn. Prefix and Version Compatibility Errors

This is the cause most tutorials skip entirely, and it's been showing up more frequently as teams move files between platforms.

If you open a workbook created in a newer version of Excel — or exported from Google Sheets — in an older version like Excel 2016 or 2019, functions that don't exist in the older version get flagged with an _xlfn. prefix. So =XLOOKUP() becomes =_xlfn.XLOOKUP() and returns #NAME? because the older version has no idea what XLOOKUP is.

The same thing happens with dynamic array functions like FILTER, UNIQUE, and SORT. Microsoft's XLOOKUP documentation confirms these functions require Microsoft 365 or Excel 2021 at minimum. If your users are on older builds, VLOOKUP is the right call — it's not a preference question if the alternative won't run on your audience's machine.

The fix is either to upgrade the Excel version, or replace the unsupported function with a version-compatible equivalent. For XLOOKUP, that means VLOOKUP or INDEX/MATCH. For FILTER, there's no clean single-function substitute in Excel 2019: you're looking at an array formula workaround.

Cross-platform compatibility is also a real issue when working between Excel and Google Sheets. Functions like IMPORTRANGE and QUERY have no Excel equivalent at all. When those land in Excel, #NAME? is what you get, and the fix is a full formula replacement, not a tweak.

---

Common Mistakes That Keep the Excel NAME Error Coming Back

Once you've fixed your current batch of errors, three habits will prevent most of them from returning.

1. Always type quote marks, never paste them. Any time you're referencing literal text inside a formula, wrap it in straight double quotes typed directly from your keyboard. This is the single most reliable way to avoid the curly-quote trap.
2. Confirm function names with Tab, not Enter. It sounds small. It isn't.
3. Use IFERROR as a fallback. The syntax is =IFERROR(your_formula, "check formula"). This won't fix an underlying #NAME? issue — if the function name isn't recognized, IFERROR can't catch it — but it will surface other formula errors gracefully instead of cascading broken values through your sheet. Think of it as a safety net, not a solution.

For a broader look at how these errors fit into Excel's error system, the Excel for beginners guide is worth a read if you're still building your foundation. And if you're running into a #VALUE! error alongside your #NAME? errors, those usually have a different root cause and are worth diagnosing separately.

If you take one thing from this article: find all your #NAME? errors before you fix any of them. A complete picture first saves you from fixing the same thing repeatedly in different cells, and makes it a lot easier to spot when one bad formula got copied across an entire column.

---