Chapter 4: Working with Excel Ranges and Tables
4
If the text contained in a cell would result in an invalid name, Excel modifies the name to make it valid. For example,
if a cell contains the text Net Income (which is invalid for a name because it contains a space), Excel converts the
space to an underscore character. If Excel encounters a value or a numeric formula where text should be, however, it
doesn’t convert it to a valid name. It simply doesn’t create a name, and it does not inform you of that fact.
If the upper-left cell of the selection contains text and you choose the Top Row and Left Column options, Excel uses
that text for the name of the entire range, excluding the top row and left column. So, after Excel creates the names,
take a minute to make sure that they refer to the correct ranges. If Excel creates a name that is incorrect, you can
delete or modify it by using the Name Manager (described next).
Managing names
A workbook can have any number of named cells and ranges. If your workbook has many
names, you should know about the Name Manager, which is shown in Figure 4.15.The Name Manager appears when you choose Formulas ➪ Defined Names ➪ Name Manager
(or press Ctrl+F3). The Name Manager has the following features:Displays information about each name in the workbook You can resize the Name
Manager dialog box, widen the columns to show more information, and even rearrange the
order of the columns. You can also click a column heading to sort the information by the
column.
Allows you to filter the displayed names Clicking the Filter button lets you show only
those names that meet certain criteria. For example, you can view only the worksheet-level
names.
Provides quick access to the New Name dialog box Click the New button to create a new
name without closing the Name Manager.
Lets you edit names To edit a name, select it in the list, and then click the Edit button.
You can change the name itself, modify the Refers To range, or edit the comment.
Lets you quickly delete unneeded names To delete a name, select it in the list and click
Delete.Be extra careful when deleting names. If the name is used in a formula, deleting the name causes the formula to
become invalid. (It displays #NAME?.) It seems logical that Excel would replace the name with its actual address,
but that doesn’t happen. However, deleting a name can be undone, so if you find that formulas return #NAME? after
you delete a name, choose Undo from the Quick Access toolbar (or press Ctrl+Z) to get the name back.