In Excel, a named range’s scope determines where its name can be used: a workbook-scoped name is available throughout the workbook, while a worksheet-scoped name belongs to one particular sheet. Choose workbook scope for a name that should mean the same thing everywhere; choose worksheet scope when a name should be limited to one sheet or may have a different meaning on different sheets.
What named range scope means
A named range is a meaningful label assigned to a cell, a range of cells, or sometimes a formula or constant. Instead of relying only on coordinates such as a cell address, a workbook can use a descriptive name such as TaxRate or MonthlySales. Scope is the part of a defined name’s identity that tells Excel where that label applies.
Scope does not change the cells themselves, restrict who can edit them, or control whether a range is visible. It is a rule for resolving the name in formulas and other workbook features. A worksheet-scoped name is associated with one sheet; a workbook-scoped name belongs to the workbook as a whole. Excel can therefore contain names with the same spelling if their scopes differ.
It helps to distinguish a name’s scope from what it refers to. The name is the label, its scope is where Excel recognizes that label, and its definition is the cell reference or expression assigned to it. Those are related but separate properties.
How workbook-scoped names work
A workbook-scoped name can generally be used in formulas on any worksheet in that workbook. For example, a workbook might have one name for its standard tax rate, shared by several calculation sheets. If the name is used in a formula on different sheets, it continues to identify the same defined item unless a same-named local name takes precedence on the current sheet.
Workbook scope is usually the natural choice for a shared input or range that has one consistent meaning throughout the file. Examples include a company-wide discount rate, a list used for data validation on several sheets, or a centrally maintained set of product categories. A single definition can make updates easier: changing the definition of a shared name can update the formulas and features that depend on it.
Workbook scope does not mean a name automatically refers to every sheet or to matching cells on all sheets. It has one definition. If each department has its own sales range, a single workbook-level name cannot represent a different range just because a formula is entered on a different department’s sheet. Use separate worksheet-scoped names for that pattern, or give each range a distinct workbook-scoped name.
How worksheet-scoped names work
A worksheet-scoped name is available by its unqualified name in formulas on the sheet to which it belongs. It can also be useful when formulas on another sheet need to refer specifically to that local name: Excel allows a sheet-qualified reference to identify which sheet’s name is intended. When sheet names contain spaces or punctuation, Excel uses the usual quoting rules for sheet references.
Local scope is particularly useful when several sheets share a layout. Imagine one worksheet for each region, with a local name called Target referring to that region’s own target cell. Each sheet can use Target in its own formulas, while the definitions point to different cells. This avoids inventing names such as NorthTarget, SouthTarget, and WestTarget for every repeated layout.
The trade-off is that a local name is not automatically available everywhere by its short name. On a different worksheet, Excel may not know which sheet’s local Target you mean. You must use the sheet-qualified name when referring to a particular sheet’s local name, or create a workbook-scoped name if the item is intended to be shared.
What happens when names have the same spelling
Excel permits a workbook-scoped name and one or more worksheet-scoped names to share the same spelling. It also permits local names with that spelling on different worksheets. This can be convenient for repeated sheet designs, but it can also make a formula’s meaning less obvious.
When a formula on a worksheet uses a name that exists both at workbook scope and at that worksheet’s local scope, the local name takes precedence on that sheet. The workbook-level name remains available elsewhere, where no matching local name overrides it. As a result, identical-looking formulas on two sheets can refer to different cells if each sheet has its own local definition.
For example, if workbook scope defines Rate once and the East and West sheets each have a local Rate, a formula using Rate on East resolves to East’s local definition. The same name on West resolves to West’s local definition. A formula on a sheet without a local Rate uses the workbook-scoped definition. This is why checking scope is important when a formula appears to use the right name but returns an unexpected result.
Create and inspect a name in Excel
In Excel for Microsoft 365 on Windows, use Formulas > Name Manager to review defined names. To create one, open Name Manager, select New, enter the name, choose its scope in the Scope list, and set the definition in Refers to. The available scope choices include the workbook and individual worksheets. Select OK to save the name.
- Open the worksheet or workbook where the name will be used.
- Choose Formulas > Name Manager, then select New.
- Enter a clear name and select Workbook or the intended worksheet in Scope.
- Check the Refers to field so the name points to the intended cells or expression.
- Select OK, then close Name Manager and check the name in a relevant formula or feature.
In Name Manager, the Scope column helps distinguish names with identical spelling. The Refers to column shows what each name represents. Select a name and choose Edit to change its name or definition. In Excel for Mac, the Name Manager is also available from the Formulas tab, although ribbon layout and dialog details can vary by release. Excel for the web may present name-management controls differently from desktop Excel; if a particular name-management option is unavailable in your web interface, use the desktop app.
Be careful when creating a name from a selection or through the Name Box: those shortcuts may not offer the same clear scope choice as the New dialog in Name Manager. If scope matters, confirm it in Name Manager rather than assuming which scope Excel assigned.
How to choose the right scope
Start by asking whether the name should have one definition for the whole file or a separate definition on each worksheet. Scope should express the design of the workbook, not merely the location where the name was first created.
- Choose workbook scope when many sheets should use the same input, list, or range, and the name should have one consistent meaning.
- Choose worksheet scope when each sheet has its own version of a repeated item, such as a local target, input cell, or calculation range.
- Prefer distinct workbook names when people often work across sheets and unqualified formulas should be easy to interpret.
- Use local names deliberately when the workbook has a repeated template and users mainly work within each individual sheet.
- Document exceptions if a local name intentionally overrides a workbook name, because that behavior may surprise someone reviewing formulas later.
There is no universal rule that one scope is safer. Workbook names are easier to reuse globally, while local names can reduce naming clutter in workbooks with many similarly structured sheets. The best choice is the one that makes the intended reference predictable to someone maintaining the workbook.
Names can refer to more than fixed cell ranges
Although the phrase named range suggests a block of cells, Excel’s defined names can also represent a formula or a constant. Scope still matters in those cases: Excel must determine which definition to use wherever the name appears. A name might represent a calculated value, for example, rather than a visibly selected rectangle of cells.
Some names refer to a fixed address, while others are designed to adjust as the workbook changes. A name’s scope does not by itself make its reference dynamic, expand it automatically, or guarantee that it covers new rows. Inspect the Refers to definition and test the name after adding or removing data. If a name is intended to track a changing table, consider whether an Excel Table or an appropriately designed reference is a better fit.
Names can also be used in places beyond ordinary cell formulas, including data validation and chart settings. Whether a name works in a particular feature can depend on how that feature handles references and scope. If a local name is not accepted or does not resolve as expected in a feature, verify the feature’s reference requirements and consider a workbook-scoped name or a direct reference where appropriate.
Common scope problems and how to fix them
A frequent problem is believing a workbook-scoped name is being used when a local name with the same spelling is actually taking priority. Open Name Manager, inspect the Scope column, and look for duplicates. If the local definition is unintended, edit or delete it; if both names are intentional, make references explicit where clarity matters.
Another common issue is trying to change an existing name’s scope in the Edit dialog. Scope is established when the name is created and generally cannot simply be switched there. To move a definition to another scope, create a new name with the desired scope, update formulas or features that refer to the old name, verify the results, and then remove the obsolete name if it is no longer needed.
Other useful checks include:
- Confirm that the name is spelled correctly and has not been deleted or renamed.
- Check whether the name refers to the intended cells, especially after inserting, deleting, or moving rows and columns.
- Look for a local name that shadows a workbook-level name on the sheet where the issue occurs.
- After copying a worksheet, inspect Name Manager for local names that came with the copied sheet.
- When a sheet is renamed or moved, review references if formulas or names display errors.
- Use descriptive names and avoid unnecessary duplicates so the workbook is easier to maintain.
Excel names must follow naming rules: they cannot contain spaces, and a name cannot be mistaken for a cell reference. Use letters, digits, underscores, or periods in a clear, compact label, and avoid names that look like addresses. These rules apply regardless of whether the scope is workbook or worksheet.
Frequently Asked Questions
Can I change a named range from workbook scope to worksheet scope?
Usually, not by editing the existing name’s scope. Create a new name at the desired scope, update references that should use it, verify the workbook, and then delete the old name if it is no longer required.
Can two worksheets have a named range with the same name?
Yes. Worksheet-scoped names can share a spelling when they belong to different worksheets. A workbook-scoped name can also share that spelling, but a same-named local name takes precedence on its own sheet.
How do I see whether a name is local or workbook-scoped?
Open Formulas > Name Manager and check the Scope column. It identifies whether each entry belongs to the workbook or to a specific worksheet.
Why does the same name return different values on different sheets?
A local name on each sheet may override a workbook-scoped name, and each local definition can refer to different cells. Check the names and their scopes in Name Manager, then qualify references or adjust the definitions as appropriate.
Should I use workbook scope or worksheet scope?
Use workbook scope for one shared definition that should mean the same thing throughout the file. Use worksheet scope when repeated sheets need separate definitions for the same kind of item.
Related
- Excel Scatter Plot: How to Create, Format, and Read It
- Excel: How to Split Text Into Two Columns Without Errors
- Excel: How to Center a Print Area on a Printed Page
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Share a Calendar in Outlook Web: Step-by-Step GuideTo share a calendar in Outlook on the web, open Calendar, find the calendar under My calendars, choose…
- How to Get Rid of All Track Changes in Microsoft WordTo get rid of tracked changes in Word, choose whether to keep or discard the proposed edits, then…
- Microsoft Excel Always Not Responding? Causes and FixesNo—Microsoft Excel does not always become unresponsive because it is broken. A brief freeze can happen while a…
- Excel Merge Cells Shortcut: Keys and Other OptionsIn Excel for Microsoft 365 on Windows, select the cells and press Alt, H, M, C—one key at…
- Excel Redo Shortcut: How to Restore an Undone ActionIn Excel for Microsoft 365 on Windows, press Ctrl+Y to redo an action you just undid. If you…
- Excel Data Validation List From a Named RangeTo make an Excel drop-down list from a named range, first define a name for the cells containing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.