In Excel for Microsoft 365 on Windows, a named range’s scope is fixed when the name is created, so Excel greys out Scope when you edit an existing name. To use a different scope, create a replacement name with the intended scope, update formulas that use the old name, and then remove the old name when it is safe to do so.
Why the scope setting is greyed out
Scope is not an ordinary editable property like the range or formula shown under Refers to. It determines where Excel recognizes a name: throughout the workbook or only on one worksheet. Excel does not provide a control to convert an existing workbook-scoped name into a worksheet-scoped name, or vice versa. The disabled field in the edit dialog is therefore expected behavior, not usually a fault with the workbook.
To inspect a name in Excel for Microsoft 365 on Windows, open Formulas > Name Manager, or press Ctrl+F3. Select the name and choose Edit to change its name definition or the cells or formula it refers to. Scope remains unavailable there. On a Mac, use the Formulas tab and open Name Manager; the same scope limitation applies.
The key distinction is whether the disabled field appears while editing an existing name or creating a new one. Editing cannot change scope. When you choose New in Name Manager, the Define Name dialog normally lets you choose Workbook or a particular worksheet in the Scope list. If Scope is unavailable in that new-name dialog too, check the conditions described below and, if necessary, use the desktop Excel app.
What workbook and worksheet scope mean
A workbook-scoped name is available across the workbook. For example, a name called TaxRate can refer to a cell containing an assumed rate, and formulas on different worksheets can use TaxRate. A worksheet-scoped name is available only on its designated sheet. A name called PrintArea scoped to a sheet, for example, is distinct from a name of the same spelling scoped to another sheet.
Worksheet scope can be useful when each sheet repeats the same layout. Imagine a workbook with January and February sheets, each containing a cell named Target. The January name can refer to January’s target cell and the February name to February’s. A formula on January that uses the unqualified name Target resolves to January’s sheet-level name. A workbook-level name, by contrast, is available throughout the workbook unless a same-named local name takes precedence on the current worksheet.
This makes scope part of how Excel interprets formulas, not just a label attached to a range. Changing it could alter which cell a formula points to. A local name may also be unavailable from formulas on other sheets unless the reference is appropriately qualified or a workbook-level name exists. That is why Excel treats scope as a creation-time choice.
How to replace a name safely
Before changing names, save a separate copy of the workbook. Then open Formulas > Name Manager and review the name’s current Refers to entry, its scope, and the formulas or other workbook features that depend on it. The replacement should refer to the intended cells or formula and have the desired scope.
- In Name Manager, record the old name, its scope, and its Refers to definition. Check whether the reference uses absolute cell addresses, a formula, or a range that expands with the data.
- Choose New. Enter the replacement name, select the intended worksheet or Workbook in Scope, and set Refers to correctly. Choose OK, then test the new name in a formula or in the relevant workbook feature.
- Update formulas and other references that need to use the replacement. Check formulas on every relevant worksheet, not just the sheet where the name was first noticed.
- Recalculate and inspect the results. Also check items such as data validation, chart series, conditional formatting, and print settings if the name is used there.
- Only after confirming that the old name is no longer needed, select it in Name Manager and choose Delete. Save the workbook and review it again.
If the intended scope already has a name with the same spelling, decide whether that existing name is the one you should retain. Excel can support identical spellings at different scopes, but a worksheet-scoped name can take precedence over a workbook-scoped name on that worksheet. This overlap can make a formula appear to use the wrong range. A distinct temporary name during migration can help you test the replacement, but remember to update and verify each reference before deleting the original.
Create a name with the desired scope
In Windows desktop Excel, open Formulas > Name Manager and select New. In the Define Name dialog, enter a valid name, choose Workbook or the specific worksheet in Scope, and enter the range or formula in Refers to. Select OK. You can also use Formulas > Define Name to open the dialog. Names cannot contain spaces and must follow Excel’s naming rules; for instance, a name cannot be written in a way Excel interprets as a cell reference.
For a worksheet-level name, choose the worksheet itself in Scope rather than Workbook. Choosing a different sheet in the tab bar is not a substitute for setting Scope in the dialog. The worksheet selection and the name’s scope are separate choices. After creating the name, return to Name Manager and confirm its scope and Refers to entry before relying on it in important formulas.
Be precise about the reference. If the old name referred to multiple cells, a formula, or a carefully chosen absolute or relative reference, do not replace it with a guessed single-cell address. A named formula may not refer to a simple range at all. Reproducing the original definition correctly is essential to preserving the workbook’s results.
Check formulas and other dependencies
Deleting a name does not safely rewrite every place it might be used. A formula that still calls a deleted name may return #NAME? until the reference is corrected. If you create a replacement with a different spelling, formulas that use the old spelling do not automatically switch to the new one. Search and review formulas, and inspect workbook features that can store names as references.
For example, suppose a workbook-level name SalesData refers to a sales range, but you want each regional worksheet to have its own SalesData. Creating local names may cause formulas on those sheets to resolve to the local ranges instead of the workbook-level range. That may be intended for regional calculations, but it can also change the meaning of existing formulas without changing their visible text. Test representative formulas on each sheet and compare the results with expected values.
Names can also be used outside ordinary cell formulas. A chart series, a data-validation list, a conditional-formatting rule, or a print area may depend on a name. Check the relevant feature after the change rather than assuming a formula search will find every dependency. If the workbook is important or complex, work on a copy and validate key outputs before replacing the original file.
If Scope is greyed out even when creating a name
First confirm that you selected New, not Edit. Scope is meant to be selected for a new name; the existing-name edit window cannot convert scope. If you are creating a new name and still cannot select a scope, make sure the workbook is open in the desktop version of Excel and that you can edit the file. A read-only or protected view can prevent changes, and web or preview experiences may offer fewer name-management controls than desktop Excel.
Excel for the web can manage some defined names, but its available controls can differ from the Windows desktop app. If you do not see a scope choice there, open the workbook in desktop Excel and use Formulas > Name Manager > New. On Mac, use the Formulas tab and Name Manager or Define Name; the option to set scope belongs to creating a name, not changing the scope of an existing one.
Workbook structure protection is sometimes blamed for this behavior, but it is important to identify the actual dialog and the exact control before changing protection settings. The standard inability to edit an existing name’s scope is expected even in an otherwise editable workbook. Do not remove protection or alter workbook settings simply to try to unlock the scope of an existing name; create a replacement instead.
Common misconceptions about named ranges
A named range is not necessarily a fixed block of cells. Its Refers to definition can be a cell or range, or it can contain a formula. Recreating a name therefore means reproducing its definition and intended scope, not merely selecting cells that look similar. A name also is not the same thing as an Excel table name: table names have their own rules and are managed through table tools, so changing a table name will not change a defined name’s scope.
Another misconception is that changing the name’s Refers to box changes its scope indirectly. It does not. Refers to controls what the name points to; Scope controls where Excel resolves that name. Likewise, renaming a name does not convert a worksheet-level name into a workbook-level name. If the scope itself must differ, create a new definition with that scope and migrate references deliberately.
Frequently Asked Questions
Can I change a named range from worksheet scope to workbook scope?
No. Excel does not let you change an existing name’s scope. Create a workbook-scoped replacement, update the references that need it, and remove the old name only after checking its dependencies.
Why is Scope disabled in Name Manager?
If you selected an existing name and clicked Edit, the disabled Scope field is normal. Scope is set when a name is created. If it is disabled in the New dialog, check that you are in editable desktop Excel and using New rather than Edit.
Can two named ranges have the same name?
Names with the same spelling can exist at different scopes, such as workbook scope and worksheet scope. A local worksheet name can take precedence on its own sheet, so duplicate spellings may change which definition a formula uses.
Will deleting a named range update formulas automatically?
Do not rely on that. Formulas that still refer to a deleted name may show #NAME?, and references in workbook features may need separate attention. Create and test a replacement, update dependencies, and only then delete the old definition.
Can I change named-range scope in Excel for the web?
Scope-management options can be more limited in the web app. If the option is missing, open the workbook in desktop Excel and create a new name with the desired scope; an existing name’s scope still cannot be edited there.
Related
- Conditional Formatting Scatter Plots in Excel
- Conditional Formatting Percentages in Excel: A Guide
- Conditional Formatting in Excel: Questions Answered
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Clear Cache Data in OutlookTo clear cache data in Outlook, first identify which Outlook version you use: in classic Outlook for Windows,…
- How to Create a Calendar Template in Word That You Can ReuseTo create a reusable calendar template in Word, make a seven-column table for the days of the week,…
- Data Validation Multiple Selection in Excel: What WorksExcel’s built-in data validation list allows one selection per cell; it does not provide a native setting for…
- Google Sheets Monthly Timesheet TemplateYes—you can make a monthly timesheet template in Google Sheets by setting up columns for date, employee, start…
- How to Keep the Ribbon Open in Excel on Windows and MacTo keep Excel’s Ribbon open on Windows, select Ribbon Display Options near the upper-right corner of the window,…
- Excel Consolidate Cells: Merge or Combine Data?In Excel, “consolidate cells” can mean merging cells into one larger cell, joining the contents of several cells,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.