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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Looking for a formula that can return the address of a cell containing a text string? Look no further; the solution is in ...
Discover MoreIf a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...
Discover MoreIt is not uncommon to reuse formulas in a variety of workbooks. If you develop some "gotta keep" formulas, here are some ...
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 © 2018 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