You may need to change a named range in Excel because a formula’s source data has moved or expanded. Perhaps you have old, unused names cluttering your workbook. The Name Manager is the central tool for managing all defined names. This article explains how to open the Name Manager and use it to modify or remove named ranges.
Key Takeaways: Managing Named Ranges
- Formulas > Name Manager: Opens the dialog box to view, edit, filter, and delete all defined names in the workbook.
- Edit button in Name Manager: Allows you to change the name’s reference, scope, or comment without altering formulas that use it.
- Delete button in Name Manager: Permanently removes a selected name; any formulas using it will display a #NAME? error.
What the Name Manager Tool Does
The Name Manager is a dialog box that lists every defined name in your Excel workbook. This includes named ranges, constants, and formulas. For each name, it shows its current value, the cells it refers to, its scope, and any comment. The scope determines where the name is recognized: either for the entire workbook or only for a specific worksheet. You must have a workbook open to use the Name Manager; it cannot manage names in closed files. Before editing, ensure you know which cells the name should currently reference to avoid breaking dependent formulas.
Steps to Modify or Remove a Named Range
Follow these steps to change the definition of a named range or delete it entirely from your workbook.
- Open the Name Manager
Go to the Formulas tab on the ribbon. In the Defined Names group, click the Name Manager button. You can also press Ctrl + F3 to open it directly. - Select the Name to Edit
In the Name Manager dialog box, scroll through the list or use the filter at the top to find the specific named range. Click once on its name to select it. - Edit the Name’s Properties
With the name selected, click the Edit button above the list. In the Edit Name dialog, you can change the name itself in the Name field. More commonly, you will change the Refers to field at the bottom. Click the small range selector icon at the right of the Refers to box, then select the new cell range on your worksheet and press Enter. Click OK to save the changes. - Delete a Named Range
In the Name Manager list, select the name you want to remove. Click the Delete button. A confirmation dialog will appear; click OK to permanently delete the name. Be aware that any existing formulas using this name will now return a #NAME? error. - Close the Name Manager
After making your edits or deletions, click the Close button. The changes take effect immediately in your workbook.
Common Mistakes and Limitations
Here are specific issues users often encounter when working with the Name Manager.
Cannot Edit or Delete a Name
If the Edit or Delete buttons are grayed out, the name is likely defined by Excel itself, such as a table name or a structured reference. You cannot edit these through Name Manager. For tables, select the table and use the Table Design tab to rename it.
#REF! Error After Editing a Name
This happens if you incorrectly edit the Refers to range and point it to cells that do not exist, like a deleted sheet. Re-open the Name Manager, select the name, and click Edit. Carefully re-select the correct worksheet and cell range for the Refers to field.
Name Manager Shows a Duplicate Name
Excel allows the same name to exist if it has different scopes. For example, “Data” can be a workbook-level name and also a worksheet-level name on Sheet1. The Name Manager will show both. Check the Scope column to distinguish them. Delete the one you do not need.
Editing vs. Deleting a Named Range
| Item | Edit a Name | Delete a Name |
|---|---|---|
| Primary Action | Change the name text, cell reference, scope, or comment | Remove the name definition entirely from the workbook |
| Effect on Formulas | Formulas using the name update automatically with the new reference | Formulas using the name show a #NAME? error |
| Best For | Updating a range that has moved or expanded | Removing obsolete or incorrect names that are no longer used |
| Recovery | Changes can be undone with Ctrl + Z immediately after | Name must be recreated manually; no automatic undo after closing the manager |
| Button in Name Manager | Edit | Delete |
You can now efficiently clean up and maintain the named ranges in your Excel workbooks. Use the filter in the Name Manager to quickly find names scoped to a specific worksheet. For advanced control, remember you can use the Refers to box in the Edit dialog to create dynamic named ranges using functions like OFFSET.