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:
- Select the range of cells that you want to name.
- Display the Formulas tab of the ribbon.
- Click the Define Name tool in the Defined Names group. Excel displays the New Name dialog box. (See Figure 1.)
- In the Names in Workbook box, enter the name of the current worksheet, an exclamation mark, and the name you want to define, as in Sheet1!MyRange.
- Click on Add.
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.
