Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Changing the Reference in a Named Range.
Using named ranges can be very handy when you are working with formulas or when you just want to keep track of what certain cells in a worksheet are used for. At some point, however, you may want to change the cells referred to by an existing named range. For instance, if a named range called MyRange refers to cells A1:A5, you may want to have it refer to cells C7:C19 instead.
There are two ways you can accomplish this task. First, you could follow these steps:
The second way you can redefine the named range is to follow these steps:
That's it; MyRange now refers to cells C7:C19 instead of A1:A5.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8234) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Changing the Reference in a Named Range.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When you've got a column full of names, you may want to get a count of how many of those names are unique. You can make quick ...
Discover MoreYou can easily set up a formula to perform some calculation on a range of cells. When you copy that formula, the copied ...
Discover MoreA common task faced by Excel users is to determine whether items in one list are also found in a different list. There are a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-02-17 05:14:33
Hi Barry.
Interesting and useful, Thanks.
I wonder why it is different? I always thought that a normal cell address like A1 was a “pseudo reserved Name” named range, so that it should work similarly to a “user defined Name” named range..
Alan
2017-02-16 05:08:47
Barry
I make it a rule when writing macros to always refer to cells on a worksheet using a named range. That way if a user (if they are allowed to) were to add or delete rows or columns the referred range remains correct as Excel will adjust the range referred to automatically. Ranges referred to explicitly in macros are not updated if rows or columns are added or deleted.
2015-12-27 11:20:09
Brian
Hi There: I have been tasked with creating a series of graphs (27 in total) for a number of health clinics for a performance dashboard. There are 370 separate rows of data, for a number of periods (about 48). I am using the OFFSET function so the graphs will populate dynamically when new data is inserted into the master table. I have used the "Create..." Named Range feature in Excel 2011 (Mac), which helped in establishing all 370 named ranges. Now I want to update each named range to reflect its corresponding OFFSET function. Is there a fast and easy way to do this, or must I go into each Named Range and update manually?
2015-07-23 08:39:45
Alpesh
Thanks it helps me lot :)
2014-09-07 05:37:20
nico bakker
I want each ledgercode changed in another code. Then i want a each code to give a category-name. Then i want sub-totals of each category, and finally a total of these sub-totals.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Comments