Named Range Sheets: How Excel Scope Works

In Excel, a named range is a label for a cell or range, and its scope determines whether the name is available throughout the workbook or only on one worksheet. To make a name sheet-specific, create it with that worksheet selected as its scope in Formulas > Name Manager > New; choose Workbook instead when the name should be shared across sheets.

What a named range means

A named range gives a meaningful name to a cell, a group of cells, or another reference. Instead of remembering that a tax rate is stored in cell B2, for example, you can refer to a name such as TaxRate. Names make formulas easier to read and can reduce errors when a workbook contains many sheets or a long list of inputs.

The phrase named range sheets can refer to two related ideas: names that point to cells on worksheets, and the scope that controls which worksheets can use a name without qualifying it. Scope is not the same as location. A name scoped to the Budget sheet can point to cells on another sheet; its scope says where the name is recognized by default, not where its values must be stored.

Names can also refer to a formula or a constant rather than a cell range. For instance, a name might represent a fixed rate or a calculation used repeatedly. In everyday spreadsheet work, though, named ranges most often identify a cell or block of cells such as an input area, a table column, or a list used for data validation.

Workbook scope versus worksheet scope

A workbook-scoped name is available throughout the file. If you define TaxRate with workbook scope, formulas on any worksheet can use TaxRate without naming a particular sheet. A worksheet-scoped name, also called a local name, is associated with one worksheet. On that sheet, a formula can usually use the local name on its own. From another sheet, refer to it with the sheet name as a qualifier.

This difference is useful when the same label makes sense in several places but represents different values. Imagine a workbook with January and February sheets, each holding a local name called Target for that month’s target cell. Each sheet can use Target to mean its own value. A single workbook-scoped Target could not represent both values under the same name.

Choose workbook scope for a value or range that should have one shared meaning throughout the file, such as a company-wide tax rate or a central list of departments.
Choose worksheet scope when a name belongs to one sheet, when separate sheets need the same name for different values, or when you want to avoid adding more workbook-wide names than necessary.

A workbook-level name and a worksheet-level name can share the same spelling. When a formula on a sheet has a local name matching a workbook name, Excel uses the local name in that sheet’s context. That can be handy, but it can also make a formula behave differently depending on which sheet contains it. Use distinct names unless the local override is intentional and documented.

Create a named range for a sheet

In Excel for Microsoft 365 on Windows, use the Name Manager to create and review names. Select the worksheet where the local name should belong, then follow these steps:

Open Formulas > Name Manager, then select New.
Enter a descriptive name in the Name box, such as LocalTarget.
In the Scope list, choose the worksheet that should own the name rather than Workbook.
Check the Refers to box. It should contain the intended cell or range reference. Select the range in the sheet if you need to correct it.
Select OK, then close Name Manager. Test the name in a formula on the intended sheet.

You can also use Formulas > Define Name to open the name-creation dialog. Check the Scope field before saving: the dialog may default to Workbook, which is not what you want for a sheet-specific name. In Name Manager, the Scope column makes it easier to distinguish workbook names from names tied to individual sheets.

Excel for the web supports creating and using defined names, but its name-management options can differ from the desktop application. If a particular scope or editing control is unavailable in the browser, open the workbook in the desktop Excel app. Excel for Mac has its own ribbon layout, but the equivalent name-management commands are on the Formulas tab; dialog labels and placement can vary slightly by release.

Use names in formulas

Once a name is defined, use it in a formula in place of the cell reference it represents. Suppose the workbook-scoped name AnnualSales refers to cells B2:B13 on the Summary sheet. With that definition in place, this formula totals the named cells:

With AnnualSales referring to Summary!$B$2:$B$13, enter this formula in any worksheet where you want the total: =SUM(AnnualSales). The result is the sum of the values in B2:B13 on Summary.

A local name can be used without qualification in formulas on the sheet that owns it. For example, if a worksheet-scoped name UnitPrice refers to D2 and Quantity refers to E2 on the Orders sheet, the following formula calculates the line amount there:

With UnitPrice referring to Orders!$D$2 and Quantity referring to Orders!$E$2, enter this formula on the Orders sheet: =UnitPrice*Quantity. If D2 contains 12 and E2 contains 3, the result is 36.

To refer to a local name from a different worksheet, qualify it with the worksheet name. For a sheet called Orders, the general form is =Orders!UnitPrice. If the sheet name contains spaces, enclose it in apostrophes, as in ='Order Details'!UnitPrice. The name itself still follows Excel’s naming rules; the apostrophes are part of the sheet reference, not the defined name.

Check and manage names safely

Name Manager is the central place to inspect defined names. Open Formulas > Name Manager and review the Name, Value, Refers to, and Scope columns. The Refers to field is especially important: a name can exist but point to the wrong cell, an outdated location, or a formula that no longer matches the workbook’s design.

Select a name and choose Edit to change its label or reference. Excel does not let you change an existing name’s scope in the edit dialog. To move a name from worksheet scope to workbook scope, or the other way around, create a replacement with the desired scope, update formulas that depend on the old name, and then remove the old one after checking the workbook.

Names can be used by formulas, charts, data validation, and other workbook features, so deleting one can have consequences beyond the visible cells. Before removing a name, inspect its references and search the workbook for formulas that use it. If the name is used in a chart or a validation rule, that dependency may not be obvious from a quick scan of worksheet formulas. Save a separate copy before making broad changes to a complex workbook.

Naming rules and practical choices

Excel names must follow naming rules. A name cannot contain spaces, and it cannot be written in a way Excel interprets as a cell reference, such as A1. Names generally start with a letter, an underscore, or a backslash; periods and underscores can be useful separators. Names are not case-sensitive, so SalesTotal and salestotal are treated as the same spelling.

Prefer names that explain purpose rather than just location. TaxRate is clearer than CellB2, while Sales_2026 is easier to interpret than Name3. Avoid reusing a label for unrelated concepts, especially when mixing workbook and worksheet scope. A short note in the sheet or a consistent naming convention can help other editors understand why local names repeat.

For data that grows over time, an Excel Table can be a better choice than a fixed named range. Tables expand as rows are added and provide structured references based on column names. For example, a table column named Amount can be referenced through the table’s structured-reference syntax rather than a manually maintained range. Tables do not replace every use for defined names, but they can reduce the maintenance needed for expanding data lists.

Common problems and how to diagnose them

The name is not recognized. Check its spelling and scope in Name Manager. A worksheet-scoped name may not be available unqualified on another sheet; add the sheet qualifier or create a workbook-scoped name if a shared name is intended.
A formula returns an unexpected value. Look for a local name that overrides a workbook name on that sheet. Then check the Refers to field to see which cells Excel is actually using.
The name refers to the wrong cells. Editing, inserting, or moving cells can change references. Review Refers to rather than assuming the name still points to the original location.
Excel rejects the name. Remove spaces and punctuation that is not permitted, and avoid names that resemble cell addresses or conflict with an existing name in the same scope.

A useful troubleshooting habit is to check scope before rewriting a formula. If an unqualified name works on one worksheet but fails or gives a different result on another, scope or a same-named local override is a likely cause. Name Manager shows both the scope and the reference, which usually narrows down the issue quickly.

Frequently Asked Questions

Can two sheets have the same named range?
Yes. Worksheet-scoped names can have the same spelling on different sheets because each belongs to its own sheet context. A formula on each sheet can use its local version. Be careful if a workbook-scoped name has the same spelling, because the local name takes precedence on its own sheet.

Can a named range refer to cells on another sheet?
Yes. Scope controls where the name is recognized by default, not which sheet its reference must point to. A local name attached to one worksheet can refer to a range on a different worksheet if that is the intended design.

How do I use a sheet-specific name from another sheet?
Qualify it with its owning sheet name, for example =Orders!UnitPrice. If the worksheet name includes spaces, put the sheet name in apostrophes before the exclamation mark, as in ='Order Details'!UnitPrice.

Can I change a named range from worksheet scope to workbook scope?
Not by editing the existing name’s scope. Create a new name with the desired scope, update any formulas or features that use the old name, confirm the replacement works, and then delete the old name if it is no longer needed.

Are named ranges the same as worksheet tabs?
No. A worksheet tab represents a sheet in the workbook. A named range is a label for a reference, formula, or constant. A name can point to cells on a sheet, but it does not create, rename, or replace that sheet.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.