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
- Select all the cells in the table.
Tip: select one cell, then press CTRL + A.
- On the Formulas tab, in the Defined Names group, click Create from Selection. The Create Names from Selection dialog appears.
- Check Top row and Left column.
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
- In a blank cell, type =.
- Type the first Defined Name followed by a space.
- Type the second Defined Name and press Enter.
Watch this ~1m video to learn more.
Previous post and additional reading: