The Name Manager

Now that you understand the concept of scope, you can start creating and using names. Excel 2007 provides a new way for maintaining names called the Name Manager, as shown in Figure 3-1.

Figure 3-1: The Excel Name Manager dialog box.

To display the Name Manager, choose Formulas Defined Names Name Manger. Within this dialog box, you can view, create, edit, and delete names. In the Name Manager main window, you can see the current value of the name, what the name refers to, the scope of the name, and any comments you've written. The names are sortable, and the columns are resizable, allowing you to see your names in many different ways. If you use a lot of names, you can also apply some predefined filters to view only the names that interest you.

Note that the Name Manager dialog box is resizable. Drag the lower-right corner to make it wider or taller.

Creating Names

The Name Manager contains a New button for creating new names. The New button displays the New Name dialog box, as shown in Figure 3-2.

Figure 3-2: The New Name dialog box.

In the New Name dialog box, you name the name, define its scope and what it refers to, and make any comments about the name to help yourself and others understand its purpose. The Refers To field is a standard RefEdit control, meaning you can select cells or type a cell reference or formula similar to how you would do it in the formula bar.

Tip 

The keyboard shortcut for displaying the Name Manager is Ctrl+F3.

Editing Names

Clicking the Edit button displays the Edit Name dialog box, which looks strikingly similar to the New Name dialog box. You can change any property of your name except the scope. If you change the name's name, all the formulas in your workbook that use that name will be updated.

Tip 

To change the scope of a name, you must delete the name and re-create it. If you're careful to use the same name, your formulas that use that name will still work.

The Edit Name dialog isn't the only way to edit a name. If the only property you want to change is the Refers To property, you can do it right in the Name Manager dialog box. At the bottom of the dialog box is the field labeled Refers To. Simply select the name you'd like to edit in the main window and change the reference in the Refers To box.

Tip 

If you edit the contents of the Refers To field manually, the status bar displays Point, indicating that you're in point mode. If you try to use keys such as the arrows, Home, or End, you'll find that you're navigating around the worksheet rather than editing the Refers To text. To switch from point mode to edit mode, press F2 and note that the status bar changes to show Edit.

Deleting Names

Clicking the Delete button permanently removes the selected name from your workbook. Of course, Excel warns you first because this action cannot be undone. Unfortunately, Excel isn't smart enough to replace deleted names with cell references. Any formulas that use a name that you delete will display the #NAME? error.