Change Address of Excel Named Range

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 the range’s address.

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.

datavalidationdropdown03

In this video you can see the steps for setting up a simple range, and then using that name in your workbook.

Or watch on YouTube: Name a Range of Cells in Excel

Changing a Name’s Range

After you create a name, you might need to change the range of cells that it refers to.

namedrangechange01

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.

datavalidationdropdown02

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.

create a table

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.

createlist01

For earlier versions, where Lists and Tables are not available, you can use an INDEX for dynamic range or OFFSET formula.

indexdynamic03

Option 2: Redefine the Named Range

For a static range, that rarely changes, you can follow these steps to change the range address:

  1. On the Ribbon, click the Formulas tab
  2. Click Name Manager
  3. In the list, click on the name that you want to change
  4. In the Refers To box, correct the range reference, or drag on the worksheet, to select the new range.
  5. Click the check mark, to save the change
  6. Click Close, to close the Name Manager

datavalidationdropdown04

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.

Or watch on YouTube: Change a Named Range in Excel

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 may also like...

4 Responses

  1. You didn’t mention option 3: convert range to table (or List in Excel 2003).

  2. Do I feel silly :-)
    Not just 2007 and up though, in 2003 you can convert the range to a list, making it dynamic too.

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *