## Numbers - Referring to Cells in Formulas

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
column

The column name followed by
the row name

2006 Revenue refers to a cell
contains Revenue.

A cell in a table that has
columns

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
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

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.