Leading the way in Microsoft Office Development

Step 1 - What are Workbook Level Named Ranges ?

Workbook level named ranges can be used anywhere within the workbook.

The most common named ranges and the easiest ones to create are workbook level named ranges.

Most named ranges are defined at workbook level.

These are prefixed with a worksheet name in the (Insert > Name > Define) dialog box.

These define a cell range on a specific worksheet and can be referenced from any worksheet within the workbook.

These named ranges are visible in the (Insert > Name > Define) dialog box and can be seen from any worksheet in the workbook.

Step 2 - Creating Workbook 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 "workbook level" named range that refers to the cell "B2" on the worksheet "Sheet1" that can be referenced from anywhere in the workbook.

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

Make sure the reference in the "Refers to" box always starts with an equal sign.

Type in the following "Book_Sheet1_B2" for the descriptive name to use.

Names in workbook - The descriptive name you want to use for the cell or range of cells.

Refers To - You can create a range name very quickly by highlighting the cells. All cell references are absolute by default and also include their worksheet name.

Add - Creates a new named range for the cell range in the refers to box. This will not close the dialog box enabling you to enter several names at once.

Delete - Allows you to remove the named range currently selected in the names in workbook list.

OK - Closes the dialog box and saves any changes you have made.

Close - Closes the dialog box.

By default, references are fixed when names are created ??

Step 3 - Using the Name Box

An alternative way to create a workbook level named range is to select the cell or range of cells and type the name directly into the Name Box. This is the drop-down box to the left of the formula bar.

The Name Box is usually used to display the address of the active cell although if the selected cell or range has a named range associated with it, then this name is displayed instead.

Select cell "B4" on the worksheet "Sheet1" and type in the following "Book_Sheet1_B4" and press Enter.

Remember to always press Enter otherwise the name will not be created.

Step 4 - Displaying the Named Ranges

Displaying the (Insert > Name > Define) dialog box will display (in alphabetical order) a list of all the named ranges in the active workbook.

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

You can also use the Name Box to the left of the formula bar to display a list that includes all the "workbook level" named ranges.

When you select a name from the Name Box the corresponding cells are selected. This can provide an easy way to move around a large worksheet.

The Name Box has a fixed width and will only display the first 20 characters or so.

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.

Select the named range from the list, edit the cell reference in the Refers to box and then press OK.

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

Step 6 - Removing a Workbook 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.

Remember that after you delete a named range any formulas that refer to that named range will return #NAME?.

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.

When deleting named ranges or rows and columns - ensure the names are not used in any functions or formulas. If they are then #REF! will appear

(Ctrl + F3) - Displays the (Insert > Name > Define) dialog box.

It is possible to create more than one name of the same cell or range of cells. The Name box will display all the names although the name displayed when the cell is selected is the first name alphabetically.

You can define names that refer to non contiguous cells. Hold down the Ctrl key to select these cells before displaying the (Insert > Name > Define) dialog box.