Change Address of Excel Named Range

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 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.

select name in Excel Name Box
select name in Excel Name Box

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.

drop down list on worksheet
drop down list on worksheet

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 named Excel table
create a named Excel 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.

create a list in Excel 2003
create a list in Excel 2003

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

Excel Name Manager
Excel Name Manager

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
Excel Name Manager command on Ribbon
Excel Name Manager command on Ribbon

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.

How to change a named range in Excel
How to change a named range in Excel

____________________

0 thoughts on “Change Address of Excel Named Range”

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.