Referring to Cells in Formulas
You use cell references to identify cells whose values you want to use in formulas. The
cells can be in the same table as the formula cell, or they can be in another table on
the same or a different sheet.
126
Chapter 6
Using Formulas in Tables
Chapter 6
Using Formulas in Tables
127
Cell references have different formats, depending on such factors as whether the cell’s
table has headers, whether you want to refer to a single cell or a range of cells, and so
on. Here’s a summary of the formats that you can use for cell references.
To refer to
Use this format
Example
Any cell in the table containing
the formula
The reference tab letter followed
by the reference tab number for
the cell
C55 refers to the fifty-fifth row in
the third column.
A cell in a table that has a
header row and a header
column
The column name followed by
the row name
2006 Revenue refers to a cell
whose header row contains
2006 and header column
contains Revenue.
A cell in a table that has
multiple header rows or
columns
The name of the header
whose columns or rows you
want to refer to
If 2006 is a header that spans
two columns (Revenue and
Expenses), 2006 refers to all
the cells in the Revenue and
Expenses columns.
A range of cells
A colon (:) between the first
and last cell in the range, using
reference tab notation to
identify the cells
B2:B5 refers to four cells in the
second column.
All the cells in a row
The row name or row-
number:row-number
1:1 refers to all the cells in the
first row.
All the cells in a column
The column letter or name
C refers to all the cells in the
third column.
All the cells in a range of rows
A colon (:) between the row
number or name of the first and
last row in the range
2:6 refers to all the cells in five
rows.
All the cells in a range of
columns
A colon (:) between the column
letter or name of the first and
last column in the range
B:C refers to all the cells in the
second and third columns.
A cell in another table on the
same sheet
Table name followed by two
colons (::) and then the cell
identifier
Table 2::B5 refers to cell B5 in
a table named Table 2. Table
2::2006 Class Enrollment refers to
a cell by name.
A cell in a table on another
sheet
Sheet name followed by two
colons (::), the table name if the
cell name isn’t unique in the
spreadsheet, two more colons,
then the cell identifier
Sheet 2::Table 2::2006 Class
Enrollment refers to a cell in a
table named Table 2 on a sheet
named Sheet 2.
You can omit a table or sheet name if the cell or cells referenced have names unique in
the spreadsheet.
When you reference a cell in a multi-row or multi-column header, you’ll notice the
following behavior:
The name in the header cell closest to the cell referring to it is used. For example, if
Â
a table has two header rows, and B1 contains “Dog” and B2 contains “Cat,” when you
save a formula that uses “Dog,” “Cat” is saved instead.
However, if “Cat” appears in another header cell in the spreadsheet, “Dog” is retained.
Â
To learn how to insert cell references into a formula, see “Using the Keyboard and
Mouse to Create and Edit Formulas” on page 128. See “Distinguishing Absolute and
Relative Cell References” on page 129 to learn about absolute and relative forms of cell
references, which are important when you need to copy or move a formula.