Refer to cells in formulas
All tables have reference tabs that identify each cell in the table. The reference tabs are at the top of each column, containing a column letter (for example, "A") and at the left of each row, containing a row number (for example, "3"). A cell, row or column can be referenced using the values in the reference tabs.
You can use cell references to identify cells whose values you want to use in formulas. You can refer to cells in the same table as the formula cell or (in Numbers) in another table on the same or different sheet.
Formats for cell references
Cell references have different formats, depending on such factors as whether the cell’s table has headers, whether you 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 55th 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. |
In Numbers, a cell in another table on the same sheet | If the cell name is unique in the spreadsheet, then only the cell name is required; otherwise, the 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 Enrolment refers to a cell by name. |
In Numbers, a cell in a table on another sheet | If the cell name is unique in the spreadsheet, then only the cell name is required; otherwise, the sheet name followed by two colons (::), the table name, two more colons, then the cell identifier | Sheet 2::Table 2::2006 Class Enrolment refers to a cell in a table named Table 2 on a sheet named Sheet 2. |
In Numbers, when you reference a cell in a multi-row or multi-column header, 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.
Distinguish absolute and relative cell references
You can use absolute and relative forms of a cell reference to indicate the cell to which you want the reference to point if you copy or move its formula. You can also use a combination of absolute and relative references, such as to create a running total (for example, =SUM($A$2:A2).
To specify the absoluteness of a cell reference (or any of its components), tap or click the disclosure triangle of a cell reference and choose an option from the pop-up menu.
Type of cell reference | Description |
|---|---|
Cell reference is relative (A1) | When its formula moves, it stays the same. However, when the formula is cut or copied and then pasted, the cell reference changes so that it retains the same position relative to the formula cell. For example, if a formula containing A1 appears in C4 and you copy the formula and paste it in C5, the cell reference in C5 becomes A2. |
Row and column components of a cell reference are absolute ($A$1) | When its formula is copied, the cell reference doesn’t change. You use the dollar sign ($) to designate a row or column component absolute. For example, if a formula containing $A$1 appears in C4 and you copy the formula and paste it in C5 or D5, the cell reference in C5 or D5 remains $A$1. |
Row component of a cell reference is absolute (A$1) | The column component is relative and may change to retain its position relative to the formula cell. For example, if a formula containing A$1 appears in C4 and you copy the formula and paste it in D5, the cell reference in D5 becomes B$1. |
Column component of a cell reference is absolute ($A1) | The row component is relative and may change to retain its position relative to the formula cell. For example, if a formula containing $A1 appears in C4 and you copy the formula and paste it in C5 or D5, the cell reference in C5 and D5 becomes $A2. |