Creating names from row and column headings

Instead of taking the time to use individual descriptive names to assign names to ranges in a standard data table, it's almost always more efficient to have Excel do all the naming for you by using a table's existing row and column headings.

To do this, select the table (including the cells with the row and column heading you want assigned) and then choose Insert → Name → Create to open the Create Names dialog box.

When you first open the Create Names dialog box, Excel automatically selects both the Top Row and Left Column check boxes:

• When the Top Row check box is selected, Excel assigns the column headings in the first row of your cell selection to the columns of data in the table.
• When the Left Column check box is selected, the program assigns the row headings in the first column of the cell selection to the rows of the table. (It also assigns the row heading in the top row of the leftmost column to all the rows of data in the entire table.)

If the top row of your table doesn't contain column headings, clear the Top Row check box. Likewise, if its first column doesn't contain row headings, clear the Left Column check box. Also, if your table uses an unusual layout in which the bottom row contains the column headings, clear the Top Row check box and select the Bottom Row one instead. Finally, if the rightmost column of your table contains the row headings, clear the Left Column check box and select the Right Column one in its place.

The range names you assign with the Create Names feature refer only to cells that contain data of the table and do not include the row and column headings at the top and left or bottom and right of the cell selection.

All the range names you assign with Create Names are added to the Name Box dropdown list (on the Formula bar), meaning that you can select their ranges in the worksheet simply by clicking their names on this dropdown list.

When assigning descriptive names to cell ranges in the Define Name dialog box, you never have to include the sheet name; just make sure that the descriptive names are unique. Likewise, when referring to range names in formulas, don't take time to add the sheet reference because Excel keeps track of this automatically.