Use a Range Name in a single sheet

Can I create a range name that only works in a single sheet?

By Neale Blackwood
Yes. To create a range name that only applies to a single sheet you type in the sheet name following by a “!” then the range name you require. To create a range name called Test in Sheet1, you would type Sheet1!Test in the Names in workbook box in the Define Name dialog box.

To quickly open the Define Name dialog box press Ctrl + F3. If you have names defined pressing F3 will display a list of names available for the active test. This is handy when creating formula using names.

Extras

Excel 2007 has a new feature called the Name Manager. This is in the Formula Ribbon tab in the Defined Names section. When creating the name you can specify the scope as sheet or workbook. Once created you can’t change it. You need to delete the name and re-create it with a different scope.