Create Names From Selection

This tip involves naming cells, also known as Defined Names. Among the many benefits, Defined Names can be used to:

• quickly navigate large spreadsheets
• easily define print areas
• simplify formula entry

In the below table you want to easily reference the data in any given column or row. Since the data is in a table you can easily create a Defined Name, for each column and row, using the Create from Selection command.

Creating Defined Names From Selection

1. Select all the cells in the table.
   Tip: select one cell, then press CTRL + A.
2. On the Formulas tab, in the Defined Names group, click Create from Selection. The Create Names from Selection dialog appears.
3. Check Top row and Left column.
4. OK.

To display the Defined Names, press F3 or click the Name Box drop-down. Defined Names will also appear as you enter formulas, preceded by the name tag icon.

 

PowerTip: The intersection of two Defined Names can be displayed using a formula.

Using Defined Names to Display the Intersecting Value

1. In a blank cell, type =.
2. Type the first Defined Name followed by a space.
3. Type the second Defined Name and press Enter.

Cheers!

hɔuᴉnb

 
Watch this ~1m video to learn more.

Previous post and additional reading:

• 2014 Emmy Awards
• Using DSUM
• Linking the Check Box to a Cell
• More Excel Tips