Substituting Labels Automatically

Removed in 2007

Substituting Existing Cell References

Excel can substitute labels for cell references in formulas automatically when you enter them.
This can only be done with new cell references and cannot be used to substitute any existing ones.
After you have defined the column and row labels (see steps below) the natural language formula "Yr 2003 Charles" will appear automatically in cell "C9".
This automatic substitution will only work by entering an equal sign and selecting the cell using the mouse.

Using the (Insert > Name > Label) dialog box

Select (Insert > Name > Label) to display the Label Ranges dialog box.
This lets you define the cell ranges that contain the headings and labels you want substituted in your formulas.

Add label range - Select the cells on the worksheet that refer to the labels you want to use in your formula. The Collapse Dialog button at the right end of this box temporarily moves the dialog box so that you enter the cell or range of cells by selecting cells on the worksheet. When you finish, you can click the button again to display the entire dialog box. The cell range that is currently selected will appear as default when the dialog box is displayed.
Add - Adds the cell or range of cells that contain label names to the Existing label ranges box.
Row labels - Click Row labels to add label names as row labels. The cell range you enter in the Add label range box can be only one column wide.
Column labels - Click Column labels to add label names as column labels.
Existing label ranges - Displays the label ranges you have added. To remove an existing label range name from the list, select the range in the box, and click Remove. Removing label ranges from the existing label ranges box does not remove the labels from the worksheet.
Remove - Removes the currently selected item in the existing label ranges list. Removing label ranges from the Existing label ranges box does not remove the labels from the worksheet.

Defining the Row Labels

The row labels that we want to use are in cells "B3:B7".

Defining the Column Labels

The column labels that we want to use are in cells "C2:F2".

Adding your formulas

This automatic replacing of cell references only works when you select the cell using the mouse.
Notice that when you select any cell in the range "C3:F7" the natural language formula will automatically appear in the formula bar.
The screen shot below displays the formula bar when you use the mouse to select cell "F6".

Typing a cell reference directly into a cell will result in it not being substituted.

Important

Any references to existing cell references will not be changed and Excel will only substitute the appropriate labels when you enter new formulas.
You must not type the cell reference into the formula manually but you must always select the cell either with the mouse or by using the arrow keys.
Using a space character to separate two range references or names is known as explicit intersection.
If you want to change the name of an existing named range then you must create a new one and then delete the old one.
You can also perform implicit intersection which occurs when a value is chosen from a range of cells based on the row or column of the cell containing the formula.