Exploring the R1C1 Cell Reference Style

Problem

You've seen the use of the A1 style of cell reference in Recipe 1.5 and are wondering if there are other ways to refer to cells.

Solution

Yes, there are other ways to refer cells, such as the R1C1 style.

Discussion

Besides using the A1 style of cell reference, you can also use the so-called R1C1 style of cell reference. This is not the default style, but in some cases it can be more intuitive or conducive to matrix operations and programming, as we'll see later.

The R1C1 style uses numbers to identify both rows and columns in a spreadsheet. For example, R1C1 refers to the cell in row 1 column 1. To use the R1C1 style you must first activate it. To do so, go to the Tools Options... menu to open the Options dialog box. Once its open, press the General tab (see Figure 1-12).

Figure 1-12. General tab in the Options dialog box

Check the box next to R1C1 reference style under the Settings heading to activate the R1C1 style. When you press OK and return to your spreadsheet, you'll see that the column headings have changed from letters to numbers, as shown in Figure 1-13.

Figure 1-13. R1C1 reference style

Also notice that Excel automatically changed the formulas. For example, the formula in cell D7 (now R7C4) was =B7*C7; now it's =RC[-2]*RC[-1], which is a relative reference in R1C1 style.

When using the R1C1 style, if you enter a cell reference like R3C5 (i.e., R followed by a number and C followed by another number), you are using absolute cell references. The equivalent in A1 style would be $E$3. Using brackets around the number following either R or C indicates relative cell references. For example, R[1]C[2] refers to the cell one row down and two rows to the right of the cell containing that reference. The cell two rows up and one row to the left would be referred to as R[-2]C[-1]. An R or C not followed by a number or a number in brackets refers to the same row or column as the cell containing the reference.