Annoyances Alley – More About Excel Range Names

Annoyances Alley is a tiny glimpse into the Annoyances series of Office 97 related books from Woody Leonhard, Lee Hudspeth, and T.J. Lee.

This is just one of the detailed tips related to optimization and customization that appears in Excel 97 Annoyances published by O’Reilly & Associates (ISBN 1-56592-309-X) and reprinted here with permission.

“The Name Box

The quickest way to add a named range to a sheet is to select the cell or cells to be named and then click on the Name Box. Type the name and press the Enter key. This creates the name and associates it with the selected cell(s).

The Name Box displays names in alphabetical order. You can’t use the Name Box to redefine a name, since the box also lets you select or type in a range name and when you hit Enter that range is automatically selected. This makes it impossible to use the Name Box to rename something, since instead of associating a new range with an existing name, the old range is selected when you press the Enter key.

Define Names

Pull down the Insert menu, click on Names, and choose Define from the cascading menu. This displays the Define Name dialog box.

You type in the name, and in the Refers to text box you can enter a reference, select one with the mouse, or you can type in a formula. The current selection in the current sheet is the default entry in the Refers to box. Click Add if you want to create another name, or OK if you’re done. The Delete button deletes the selected name, but there is no warning or confirmation message, so be careful with this command. If you realize your error, you can use the Edit Undo command to restore the name.

One potential annoyance: the Refers to text box suffers from a limit of 255 characters so if you have a very complex set of range coordinates you might exceed this limit. Excel just truncates your reference without warning, so beware!

Name an entire row or column by just selecting the rows or columns (or both) you want and creating the name. This is how Excel keeps track of print titles, those rows and/or columns that are printed on each page of a report. Excel just takes the coordinates you enter into the Sheet tab of the File / Page Setup dialog, and creates a range name for the current sheet called Print_Titles. There are several names that have special meaning to Excel like Print_Titles, Print_Area, Database, Criteria, Data_form, Extract, Consolidate_Area, Sheet_Title, and a slew of names beginning with the prefix Auto_ that are used in triggering macros when specific events occur — like opening a particular workbook.

By default, the cell references in the Refers to text box are entered in their absolute form (note the dollar signs in the cell reference). We’ll discuss relative and absolute cell references shortly and revisit their effect on range names at that point.”