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.