Cell References

Each set of horizontal cells in a worksheet is a row, and each set of vertical cells is a column. A cell's row and column combination designates the location of that cell.

A cell reference designates one or more cells on the same worksheet. Using references, one can:

  • Use data contained in different parts of the same worksheet in a single formula.

  • Use the value from a single cell in several formulas.

  • Refer to cells on other sheets in the same workbook, and even to other workbooks. (References to cells in other workbooks are called links.)

There are two cell reference styles: A1 and R1C1.

  • In the A1 reference style, each row has a numeric heading numbered sequentially from the top down, starting at 1. Each column has an alphabetic heading named sequentially from left-to-right, A–Z, then AA–AZ, BA–BZ, …, ZA–ZZ, AAA–AAZ, ABA–ABZ, and so on. Column letters are not case-sensitive.A relative reference to a single cell is written as its column letter immediately followed by its row number. A relative reference to a whole row is written as its row number. A relative reference to a whole column is written as its column letter. A reference to a range of two or more cells is written as two single-cell references separated by the binary range operator (:). An absolute A1 reference is made up of a cell's column letter followed by its row number, with each being preceded by a dollar character ($). For example, A2, B34, and B5:D8 are relative A1 references. $A$2, $B$34, and $B$5:$D$8 are absolute A1 references. $A2, B$34, and $B5:D$8 are mixed A1 references.

  • In the R1C1 reference style, each row has a numeric heading numbered sequentially from the top down, starting at 1. Each column has a numeric heading numbered sequentially from left-to-right, starting at 1.A whole row is referenced by omitting the column, and a whole column is referenced by omitting the row. An absolute row or column reference uses absolute row or column numbers, respectively. A relative row or column reference uses, respectively, row or column offsets from the cell containing the formula, with a negative offset indicating a row to the left or a column above, and a positive offset indicateing a row to the right or a column below. Specifying an offset of zero is equivalent to omitting that offset and its delimiting brackets. For example, R[-2]C refers to the cell two rows up and in the same column, R[2]C[2] refers to the cell two rows down and two columns to the right, R2C2 refers to the cell in the second row and in the second column, R[-1] refers to the entire row above the active cell, and R refers to the current row.

The R1C1 alternate reference style can only be used at runtime.