In Excel, you can give a name to a range of cells, then use that name in a formula, or to create a drop down list in a cell. Later, if you decide that the range should be bigger or smaller, you can change address of Excel named range.
In the screen shot below, the list of names is a named range – EmpList. Select that name in the Name Box, and Excel selects the range on the worksheet.
In this video you can see the steps for setting up a simple range, and then using that name in your workbook.
Changing a Name’s Range
After you create a name, you might need to change the range of cells that it refers to.
Maybe you’ve added a row or two, or you’d like to include another column. Someone asked how to do that, in the comments on that YouTube video.
- "How can you get rid of the name you gave to the cells? I have 2 cells selected and named, when I add a third cell and select all three cells, it doesn't allow me to rename it."
In the screen shot below, a new name has been added to the end of the list, and it doesn’t show up in the drop down, which is based on the EmpList range. As the commenter noted, you can’t just select a new range of cells on the worksheet, and give them an existing name.
Option 1: Dynamic Ranges
In Excel 2003, select a cell in the list, and press Ctrl + L, to create a List. A named range based on a List will automatically include new items.
Option 2: Redefine the Named Range
For a static range, that rarely changes, you can follow these steps to change the range address:
- On the Ribbon, click the Formulas tab
- Click Name Manager
- In the list, click on the name that you want to change
- In the Refers To box, correct the range reference, or drag on the worksheet, to select the new range.
- Click the check mark, to save the change
- Click Close, to close the Name Manager
Video: Change a Named Range
To see the steps, please watch this short video. It shows you how to set up the name, create a drop down list, and then change the name's range of cells.
More Information on Names
There’s a page on my Contextures site with more information on named ranges, including the rules for range names.
You can also see how Roger Govier uses dynamic names based on tables, to create dependent drop down lists on the worksheet.