How to change a named range in Excel
To change a named range in Excel, open the Name Manager by pressing Ctrl + F3 (Windows) or Cmd + Fn + F3 (Mac), select the range name you want to edit, and modify either the name itself or the cell reference it points to. You can also access Name Manager through the ribbon: on the Formulas tab, click Name Manager in the Defined Names group. After you make your change, click OK to save it.
Named ranges are shortcuts that let you refer to a group of cells by a word instead of a cell address—for example, using SalesTotal instead of $A$1:$A$50 in a formula. When you change a named range, any formula already using that name updates automatically to point to the new cells or use the new name.
Key Takeaways
- Open Name Manager with Ctrl + F3 on Windows or Cmd + Fn + F3 on Mac, then select the range and click Edit.
- You can change the name itself, the cell reference it points to, or both at the same time.
- All existing formulas using that named range update automatically once you save the change.
- If you try to rename a range to a name that already exists, Excel will warn you and prevent the duplicate.
Opening Name Manager and locating your range
The fastest way to reach Name Manager is the keyboard shortcut. On Windows, press Ctrl + F3. On Mac, press Cmd + Fn + F3. A dialog box opens showing all named ranges in your workbook.
If you prefer the ribbon, go to the Formulas tab at the top, then click Name Manager in the Defined Names group. The dialog that opens is identical to the keyboard shortcut version. Scroll through the list to find the range you want to change. The list shows the name in the left column, the cell reference in the middle, and the scope (usually "Workbook") on the right.
Editing the range name
Click the named range you want to change, then click the Edit button. A smaller dialog opens with two fields: Name and Refers to. The Name field holds the current name of the range. To change it, clear the field and type the new name.
Excel named ranges follow these rules: they must start with a letter or underscore, cannot contain spaces, and cannot match a cell reference like A1 or Z99. Use underscores or capital letters in the middle of the name if you want to separate words—for example, Sales_Total or SalesTotal. If you try to use a name that already exists in your workbook, Excel will show an error message and prevent you from saving.
Changing the cell reference a range points to
In the same Edit dialog, the Refers to field shows which cells the named range currently points to. To change it, click in that field and modify the cell reference. You can type a new range directly—for example, change $A$1:$A$50 to $A$1:$A$100—or click the collapse button (a small arrow icon) to select the cells on your worksheet instead.
If you use the collapse button, the dialog shrinks and you can click and drag to highlight the new cells you want the range to point to. When you are done selecting, click the expand button (the arrow again) to return to the full dialog. This method is often easier than typing the reference by hand, especially if the range is large or scattered across multiple areas.
Saving your changes and verifying the update
Once you have entered the new name, the new cell reference, or both, click OK in the edit dialog. You return to the Name Manager window. Click OK again to close Name Manager and save your changes to the workbook.
Any formula that was already using the old named range now points to the new cells or uses the new name automatically. You do not need to edit formulas by hand. If you want to verify the change worked, open Name Manager again and check that the range now shows the updated information. You can also click on a cell containing a formula that uses the named range and look at the formula bar to confirm it is using the correct name.
When you cannot change a named range
Some named ranges are locked and cannot be edited. This happens when a range is part of a protected workbook or sheet. If you try to edit a range and the Edit button is grayed out, the workbook protection is preventing changes. You will need to unprotect the workbook first through the Review tab, then return to Name Manager to make your edit.
Built-in named ranges that Excel creates automatically (such as Print_Area or Print_Titles for print settings) can usually be edited the same way as ranges you created yourself. However, if you delete a built-in range by mistake, you can recreate it by setting up the print area or print titles again through the Page Layout tab.
Renaming a range without changing what it points to
If you only want to change the name and keep the cells the same, open Name Manager, select the range, click Edit, and change only the Name field. Leave the Refers to field untouched. This is useful when you realize a name no longer describes what the range contains, or when you want to follow a naming convention you did not use before.
For example, if you have a range called Q1Sales that you now want to call FirstQuarterRevenue, you can make that change without affecting any formulas. The formulas will automatically start using the new name in the formula bar, though the calculation stays exactly the same.
Frequently Asked Questions
What happens to formulas when I change a named range?
All formulas using that named range update automatically. If you change the name, the formula bar will show the new name. If you change the cells the range points to, the formula will calculate using the new cells. You do not have to edit the formulas yourself.
Can I change a named range to point to cells on a different sheet?
Yes. In the Refers to field, include the sheet name before the cell reference, like Sheet2!$A$1:$A$50. You can type this directly or use the collapse button to navigate to the other sheet and select the cells there.
What if I accidentally give two ranges the same name?
Excel prevents this. If you try to save a name that already exists, you will see an error message. You must either choose a different name or delete the existing range with that name first through Name Manager.
Can I change a named range to include more rows or columns?
Yes. Open the range in the edit dialog and modify the cell reference to expand the range. For example, change $A$1:$A$50 to $A$1:$A$100. Any formula using that range will now include the additional cells in its calculation.
Do I need to save my workbook after changing a named range?
The change is saved within the workbook as soon as you click OK in Name Manager, but you should save the file itself to preserve the change to disk. Use Ctrl + S or Cmd + S to save.