Leading the way in Microsoft Office Development

Step 1 - What are Worksheet Level Named Ranges ?

Worksheet level named ranges are worksheet specific and are normally only used on the worksheet where they have been defined.

They do allow you to use the same named range on multiple worksheets but they are only displayed when that particular worksheet is active.

The (Insert > Name > Define) dialog box only displays "worksheet level" named ranges for the active worksheet.

These can be created by preceding the named range with an exclamation mark followed by the name of the worksheet.

It is possible to refer to these from other worksheets, but they must be preceded with the worksheet name (e.g. =Sheet1!Named_Range).

Using worksheet and workbook level names in the same workbook can get a bit confusing.

Step 2 - Creating Worksheet Level Named Ranges

Select (Insert > Name > Define) to display the Define Name dialog box.

An alternative way to display this dialog box is to use the shortcut key (Ctrl + F3).

This allows you to define and apply new names, change existing names and remove names.

Lets create a "worksheet level" named range that refers to the cell "B3" on the worksheet "Sheet2" that can be referenced from anywhere in the workbook.

To define a worksheet level name you must precede the descriptive name with the name of the worksheet, followed by an exclamation mark.

Select the cell or range of cells you want to add a descriptive name to, in this case select "B3" on worksheet "Sheet2".

Type in the following "Sheet2!Sheet2_B3" for the descriptive name.

The address of the active cell or range will appear in the "Refers to" box initially.

If the worksheet name contains any spaces then the worksheet name must be enclosed in single quotation marks.

Remember to use the arrow keys to manoeuvre within the named range formula and not the mouse.

It is possible to create a worksheet level named range with the same name as that of a workbook level named range although it is not recommended.

The worksheet level named range always takes precedence, but obviously only on that particular worksheet.

Step 3 - Using the Name Box

An alternative way to create a worksheet level named range is to select the cell or range of cells and type the name directly into the Name box.

Remember to precede the descriptive name with the name of the worksheet, followed by an exclamation mark.

You must always press Enter otherwise the name will not be created.

Step 4 - Displaying the Named Ranges

These only appear in the Name box when that particular worksheet is active.

When you display the (Insert > Name > Define) dialog box any worksheet level named ranges have their respective worksheet named on the left hand side.

Worksheet level named ranges will only appear in this dialog box when the current worksheet is active.

You can alternatively use the Name Box to the left of the formula bar. The named range will only appear when that particular worksheet is active.

If you have defined a workbook and a worksheet level named range with the same name then only the worksheet level named range will be displayed when that particular worksheet is active.

Step 5 - Editing Named Ranges

You cannot use the Name Box to redefine any existing named ranges. This has to be done from the (Insert > Name > Define) dialog box.

You can redefine your named ranges using the (Insert > Name > Define) dialog box.

Select the named range from the list and edit the cell reference in the Refers to box.

You can either type in the new reference or you can select a range of cells directly.

Step 6 - Removing a Worksheet Level Named Range

Display the (Insert > Name > Define) dialog box.

Select the named range from the list and press Delete.

If you want to rename a named range then you can select (Insert > Name > Define) and change the name in the text box.

Step 7 - Things to Remember

Avoid copying a formula that includes a named range from one workbook to another as this creates a hidden link between the two workbooks

If you have formula using a named range and then delete the named range, the formula will return the #NAME? Error.

It is possible to use worksheet specific named ranges on other worksheets by prefixing the named range with its worksheet name (e.g. Sheet2!Sheet2_B3).

Only the worksheet level named ranges on the active worksheet are displayed in the Name Box and in the (Insert > Name > Define) dialog box.

If you create a named range and then realise that you want to change the name you must delete the old name and create the named range again.

If the named range already exists you cannot use the Name Box to change the reference.