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: Using the Same Range Name on Different Worksheets.
One of the handy features of Excel is that you can define names that refer to ranges of cells. (This is a big plus when you want to write formulas that make sense.) When you create a named range, Excel assumes that you want the name to be available from every worksheet within a workbook. You can, however, specify that a name be valid only for the current worksheet. In this way you can define the same name on different worksheets in your workbook. Thus, you could have a range named MyRange on Sheet1, a range named MyRange on Sheet2, and also on Sheet3. To create names that are only applicable to a specific worksheet, follow these steps:
Figure 1. The New Name dialog box.
That's it. Now, if you go to a different worksheet, the name you defined will not be available from that worksheet—only from the worksheet in which it was defined.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2662) 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: Using the Same Range Name on Different Worksheets.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Tap the Insert key and you can start overwriting information already in a cell. If you don't want to do this, one way to ...
Discover MoreIf your worksheet gets big enough, it is easy to spend a lot of time navigating back and forth between different areas. ...
Discover MoreUsing the keyboard to switch between Excel spreadsheets.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-04-08 02:38:16
@Santiago
When I tried that... the Name appears to have been changed autommatically to reflect the new worksheet name
Alan Elston
2018-04-06 13:00:34
Santiago
What if you change the sheet name afterwards?
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 © 2019 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