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. [GAn implementation is encouraged to not unnecessarily limit the number of rows and columns in a worksheet. ]

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

A cell reference has the following form:

cell-reference:name[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] A1-reference[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] A1-reference : A1-reference[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] R1C1-reference[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] R1C1-reference : R1C1-reference

workbook-name:book-name-start-character [ book-name-characters ]

book-name-start-character: any character except ', *, [, ], :, and ?

book-name-characters:book-name-characters book-name-character

book-name-character:any character except *, [, ], :, and ?

sheet-name:sheet-name-start-character [ sheet-name-characters ]

sheet-name-start-character: any character except ', *, [, ], \, :, /, and ?

sheet-name-characters:sheet-name-characters sheet-name-character

sheet-name-character:any character except *, [, ], \, :, /, and ?

A relative cell reference is based on the relative position of the cell that contains the formula and the cell to which the reference refers. If the position of the cell that contains the formula changes, the reference is changed along with it.

An absolute cell reference always refers to the absolute location of a cell. If the position of the cell that contains the formula changes, the absolute reference remains the same.

A mixed cell reference has either an absolute column and relative row, or an absolute row and relative column.

It is possible to process the same cell or set of cells on multiple worksheets within a workbook, using a 3-D reference. A reference of this type is made up of the cell reference, preceded by a range of worksheet names, and an exclamation mark character (!), in that order. A 3-D reference can be used to refer to cells on other sheets, to defined names, and to create formulas by using the following functions: AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, SUM, VAR, VARA, VARP, and VARPA.

3-D references shall not be used in multi-cell formulas.

By default, a cell reference is understood to refer to one or more cells in the current worksheet. However, a cell reference can be preceded by its parent worksheet name and an exclamation mark (!), in that order. This allows cells in one worksheet to be referenced in another worksheet of the same workbook.
example: : The cell reference MonthlyTotals!D1:D12 might be used from within a sibling (or the same) worksheet of MonthlyTotals to refer to those 12 cells.
An area is a set of rectangular-shaped contiguous cells. An area can be a single cell.
example: : A5 and B6:C10 each designate one area, and D3:D5,E12:F15 designates two areas (the comma (,) being the union operator).
Note: : The number of areas designated by a cell reference can be obtained by calling the function AREAS (§
3.17.7.10). ]

There are two cell reference styles: A1 (§3.17.2.3.1) and R1C1 (§3.17.2.3.2).