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
If the range will change frequently, it’s better to set up a dynamic range, which will adjust automatically. You can create an Excel Table to do this in Excel 2007 or later.
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.
For earlier versions, where Lists and Tables are not available, you can use an INDEX for dynamic range or OFFSET formula.
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, then create a drop down list on the worksheet, 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.
____________________
You didn’t mention option 3: convert range to table (or List in Excel 2003).
@Jan Karel, thanks, and actually that was Option 1, so I added a picture, to make it stand out more.
Do I feel silly 🙂
Not just 2007 and up though, in 2003 you can convert the range to a list, making it dynamic too.
@Jan Karel, thanks, and I’ve added the 2003 List instructions too.
I appreciate your comments — obviously that section needed a bit more information!