Excel Named Range Error? Causes and Fixes

An Excel named range error usually means the name is invalid, unavailable in the current worksheet or workbook, or points to cells that no longer exist. In Excel for Microsoft 365 on Windows, check the name and its scope in Formulas > Name Manager, then correct its spelling or reference; if Excel rejects a name as you type it, check the naming rules below.

What an Excel named range error means

A named range is a human-readable name assigned to a cell, range, formula, or constant. Instead of referring to B2:B13 in a formula, for example, you might use a name such as MonthlySales. Names can make formulas easier to read, and they can be used in formulas, data validation, and charts.

Not every problem appears as the same error. Excel may reject a name immediately with a message such as “The syntax of this name isn’t correct.” A formula containing a misspelled or unrecognized name may return #NAME?. A name that once pointed to a valid range but now points to deleted cells may show #REF! in Name Manager or cause formulas that use it to fail. Finding which symptom you have is the first step toward the right repair.

For a working example, assume B2:B4 contains 120, 80, and 100, and the name Sales_Q1 refers to B2:B4 on the same worksheet. The formula =SUM(Sales_Q1) returns 300. If the name is spelled differently in the formula, or is not available on that worksheet, Excel may not recognize it.

Excel naming rules to check

Excel names have stricter rules than ordinary labels in a worksheet. A name cannot contain spaces or look like a cell reference. Names are not case-sensitive, so Sales_Q1 and sales_q1 count as the same name within a scope.

A name must begin with a letter, an underscore, or a backslash. The remaining characters can include letters, numbers, periods, and underscores.
Spaces and most punctuation marks are not allowed. Use an underscore or a period to separate words, such as Sales_Q1.
A name cannot be a cell reference such as A1 or XFD1048576. The single letters R and C are also reserved in some reference contexts.
A name can be up to 255 characters long.
Two names in the same scope cannot have the same name, even if their capitalization differs.

If Excel refuses a name, try a short name beginning with a letter, such as Sales_2026, and avoid spaces, hyphens, and cell-like strings. A worksheet heading such as “North Region Sales” is a perfectly ordinary label, but it cannot be used unchanged as a defined name.

Find and inspect a name in Name Manager

In Excel for Microsoft 365 on Windows, open Formulas > Name Manager, or press Ctrl+F3. Name Manager shows defined names, their scope, and what each name refers to. Select a name and inspect the Refers to field: it should identify the intended cells, formula, or value, not an old sheet or a deleted range.

When using a reference, compare the sheet and cell addresses with the workbook. For instance, if a name is meant to cover the values in B2:B13 but its reference points to C2:C13, the name itself may be valid while returning the wrong data. If you change the reference, select the name, choose Edit, correct the Refers to field, and select OK. Check formulas that use it afterward.

You can also select the name in the Name Box, the box to the left of the formula bar, to jump to its referred range. This is useful for confirming that a name selects the cells you expect. It does not replace checking scope or the complete reference in Name Manager.

On Excel for Mac, use the Formulas tab’s Name Manager or Define Name commands; ribbon wording and placement can vary slightly by release. Excel for the web can work with names in formulas, but available name-management tools can differ from the desktop app. If a name needs detailed repair, open the workbook in the desktop version.

Repair an invalid or missing name

Use the symptom to guide the fix. A rejected name needs a valid spelling; a formula error needs its name checked against the defined names; and a broken reference needs the target range restored or updated.

Check the spelling. Compare every character in the formula with the name shown in Name Manager. Names ignore capitalization, but an underscore, digit, or missing letter still matters.
Confirm the name exists. If it is missing, create it through Formulas > Define Name or Name Manager > New. Enter a valid name, choose the intended scope, and set Refers to to the correct range or expression.
Check the reference. If the Refers to field contains #REF! or points to the wrong cells, edit it to refer to the correct, existing cells. If the original cells were deleted, restore them if possible or choose a replacement range deliberately.
Check where the name is available. A worksheet-scoped name may not be recognized from a different sheet unless it is qualified with its worksheet name.
Recalculate and review dependent results. Press F9 to recalculate the workbook if needed, and check formulas, charts, and validation rules that depend on the repaired name.

Do not assume that creating a new name with the same spelling will repair every formula. A name can exist but point to the wrong cells, and a second name with the same visible spelling might have a different worksheet scope. Check the existing definition before adding another one.

Workbook scope and worksheet scope

A name’s scope determines where Excel can use it. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name is available only on its particular sheet, which lets different sheets use the same local name for different ranges.

For example, a workbook-scoped name called TaxRate can be used in formulas on multiple sheets. If Sheet1 also has a worksheet-scoped name called TaxRate, that local name takes precedence on Sheet1. The workbook-level name is still available elsewhere. This overlap can make a formula behave differently after it is copied to another sheet, even though the text of the formula has not changed.

When referring to a worksheet-scoped name from elsewhere, qualify it with the sheet name, as in Sheet1!LocalSales. If the worksheet name contains spaces, enclose it in single quotation marks, for example ‘Sales Data’!LocalSales. Scope is set when a name is created and cannot simply be switched in the Edit dialog. To change scope, create a replacement name with the required scope, update dependent formulas, and remove the old name only after checking its uses.

Broken references and #REF! errors

A name can become broken when its cells or worksheet are deleted, or when workbook structure changes leave its reference invalid. In Name Manager, a Refers to entry containing #REF! is a strong clue. The formula using that name may then return an error or an unexpected result, depending on how the name is used.

Repair the reference by editing the name to point to the intended existing cells. If the sheet was renamed rather than deleted, inspect the reference carefully: Excel often updates references during a rename, but a deleted sheet cannot be recovered just by changing the text of the name. If you are unsure which cells were intended, check formulas, charts, and other workbook documentation before replacing the target.

Some names are created for particular workbook features, including print areas. Before deleting an unfamiliar name, confirm that it is not used by a print setting, chart, formula, or other workbook feature. Removing a definition can break those dependencies even if no visible formula currently displays an error.

Create names carefully and prevent repeat errors

For a straightforward range, select the cells, click the Name Box, type a valid name, and press Enter. You can also use Formulas > Define Name to set the name and scope explicitly. If using Name Manager > New, check the scope before saving; choosing the wrong scope can make a correctly spelled name unavailable where you need it.

When creating a name from worksheet labels, Formulas > Create from Selection can generate names based on selected row or column headings. Review the results afterward: headings may contain spaces or punctuation, and Excel may adjust them to make valid names. For a workbook that will be shared, choose clear, consistent names and avoid creating unnecessary local and workbook-level names with identical spellings.

Use descriptive names such as UnitPrice or Sales_Q1 rather than vague names like Data1.
Check the Refers to field after inserting or deleting rows and columns, especially in older or frequently edited workbooks.
When copying formulas between sheets, consider whether they rely on local or workbook-scoped names.
Before deleting or replacing a name, review its dependents and save a backup if the workbook is important.

Named ranges are not limited to cell ranges. A defined name can represent a formula or constant as well. That flexibility is useful, but it means that seeing a name in Name Manager does not prove it refers to a block of cells. Read its definition and assess whether it is the kind of name the formula expects.

Frequently Asked Questions

Why does Excel say the syntax of this name isn’t correct?
Usually the proposed name contains a space or unsupported character, begins with an invalid character, or resembles a cell reference. Try a name such as Annual_Sales that starts with a letter and contains only letters, numbers, and underscores.

Why do I get #NAME? when a named range exists?
Check the spelling in the formula, then check the name’s scope. The definition may be worksheet-scoped on another sheet, or the name may refer to a formula or value different from what you expect. Name Manager helps distinguish these cases.

Can two sheets use the same named range?
Yes. Worksheet-scoped names can share a spelling across different sheets. A workbook-scoped name can also share that spelling, but a same-named local definition takes precedence on its own sheet, so the result can depend on where a formula is entered.

Can a named range contain spaces?
No. Use an underscore or a period instead, such as Customer_List or Customer.List. A worksheet label can contain spaces even though a defined name cannot.

How do I fix a name that shows #REF!?
Open Formulas > Name Manager, select the name, and inspect Refers to. Edit it to point to the intended existing range, or restore the missing sheet or cells if appropriate. Check dependent formulas and workbook features after the change.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.