Home > Excel for Business > 

An Excel 2007 Tutorial:

Local Excel Range Names

Myron,

I'm sorry that I didn't make that process clear.

In Excel, a name can be global to a workbook or local to a worksheet. The traditional method we all use when we create a range name usually  creates global names.

But when we work with dashboards we set up each chart to be supported by its own worksheet. These worksheets are very similar, with many names in common.

Here's how we set up a workbook with names that apply only to a worksheet, rather than to the entire workbook:

For convenience, I list all the names that will be defined locally. Usually, I enter these in the Control sheet. Then I first define these names globally.

Suppose that the list is in the range A20:A30. You would select the range A20:B30 and choose Insert, Name, Create. Make sure that only Left Column is checked. Then choose OK.

Then you would define the names locally in each appropriate worksheet. To illustrate, suppose you want to define a local name in sheet A. You can do so in two ways.

First, you can select the cell you're naming and choose Insert, Name, Define. For example, rather than assigning the name "ScaleType" you assign:

A!ScaleType

That is, before the name you enter the sheet name followed by an exclamation mark. (If the sheet has spaces or numbers, you also need single quotes. This is why I try to avoid spaces and numbers in my sheet names.)

Second, you can assign the name using Create Names. You take the same approach you normally would. But because we've already defined ScaleType globally (on the Control sheet) Excel automatically defines the name in sheet A as a local name.

To assure yourself that the name has, indeed, been defined locally, select sheet A and then choose Insert, Name, Define. When you scroll down to ScaleType you should see "A" at the far right side of list box in the same row as ScaleType. If you select sheet B, you'll see "B" under similar conditions. But if you select the Control sheet, you'll NOT see the sheet name, because we assigned that name globally.

By the way, if you're in, say, sheet B and you want to refer to the name as it's defined locally for sheet A, you just refer to the name like this:

=A!ScaleType

In sheet A, of course, you just refer to it as:

=ScaleType

Hope this helps,

Charley Kyd
June, 2006

(Email Comments)