Microsoft Office Tutorials and References
In Depth Information
Using cell references in formulas
Figure 12-1 Enter external references easily by clicking the cell to which you want to refer.
Understanding the row-column reference style
In the regular A1 reference style, rows are numbered and columns are designated by
letters. In the R1C1 reference style, both rows and columns are numbered. The cell
reference R1C1 means row 1, column 1 ; therefore, R1C1 and A1 refer to the same cell.
Although the R1C1 reference style isn’t widely used anymore, it was the standard in
some classic spreadsheet programs, such as Microsoft Multiplan.
To turn on the R1C1 reference style, click the File tab, click Options, select the Formulas
category, select the R1C1 Reference Style check box, and then click OK. The column
headers change from letters to numbers, and the cell references in all your formulas
automatically change to R1C1 format. For example, cell M10 becomes R10C13, and cell
XFD1048576, the last cell in your worksheet, becomes R1048576C16384.
In R1C1 notation, a relative cell reference is displayed in terms of its relationship to the
cell that contains the formula rather than by its actual coordinates. This can be helpful
when you are more interested in the relative position of a cell than in its absolute
position. For example, suppose you want to enter in cell R10C2 (B10) a formula that adds
cells R1C1 (A1) and R1C2 (B1). After selecting cell R10C2, type an equal sign, select
cell R1C1, type a plus sign, select cell R1C2, and then press Enter. When you select cell
R10C2, the formula =R[–9]C[–1]+R[–9]C appears in the formula bar. Negative row and
column numbers indicate that the referenced cell is above or to the left of the formula
cell; positive numbers indicate that the referenced cell is below or to the right of the
formula cell. The brackets indicate relative references. This formula reads, “Add the cell
nine rows up and one column to the left to the cell nine rows up in the same column.”
A relative reference to another cell must include brackets. Otherwise, Excel assumes
you’re using absolute references. For example, if you select the entire formula we
created in the previous paragraph in the formula bar and press F4, the formula changes to
=R1C1+R1C2 using absolute references.