You can name individual cells, cell ranges, and cell groups. These names can be used in formulas and functions instead of the usual cell references.
To insert a cell name:
Select the cell(s) that you want to name. For help on selecting cells, see Selecting Cells.
Go to the Insert menu and move your mouse over Name.
A menu will pop up. Click Define.
The Define Name window will appear.
Click on the box below Name, then type in the name for your cell(s). Ensure that this name has not already been used. Note also that:
Names can contain uppercase and lowercase letters.
Names can contain numbers, but cannot begin with numbers.
Names can contain underscores.
Names cannot contain spaces.
Names cannot contain symbols.
The formula for the name appears in the Formula box. If necessary, this formula can be edited by typing in the box.
When you are satisfied with the name and formula, click Add. The name you entered will appear in the box below.
To return to your spreadsheet, click OK.
Using Cell Names in Formulas
To use a cell name that you have defined, simply type the name in place of the usual cell reference.
Examples:
Suppose that you have given cell A2 the name TaxRate. Suppose also that you wanted to multiply this value by the value in B3. Instead of entering the formula =A2*B3, you could enter =TaxRate*B3.
Suppose that you have given cell range B2:B18 the name Savings. Supposed also that you wanted to find the sum of this range. Instead of entering the formula =SUM(B2:B18), you could enter =SUM(Savings).
Using Cell Names with Lookup Tables
Suppose that, in the table below, cells A2:A5 are named Names, cells B2:B5 are named Ages, and cells C2:C5 are named Birthdays. Suppose also that the entire table, A2:C5, is named Table.
|
A |
B |
C |
1 |
Name |
Age |
Birthday |
2 |
Alex |
14 |
Jun 12 |
3 |
Bob |
11 |
Apr 5 |
4 |
Eve |
13 |
Oct 16 |
5 |
Jane |
9 |
Feb 22 |
Formula |
Result |
=LOOKUP("alex", A2:A5, B2:B5) |
14 |
=LOOKUP("alex", Names, Ages) |
14 |
=LOOKUP("Eve", Names, Birthdays) |
Oct 16 |
=LOOKUP("Apr 5", Birthdays, Names) |
Bob |
=VLOOKUP("Jane", A2:C5, 2) |
9 |
=VLOOKUP("Jane", Table, 2) |
9 |
=VLOOKUP("Jane", Table, 3) |
Feb 22 |