R1C1 – Style Formulas

I’m sure that this style is alien to 99% of the Excel Users.

Anyone who uses the Excel interface would agree that the R1C1 style is dead. However, the macro recorder records formulas in the R1C1 style. Hence, we just need to learn R1C1 addressing so that we can easily read the recorded code and switch it back to the familiar A1 style.

Switching Excel to Display R1C1-Style References

(Applies to MS-Excel 2007 & 2010)

To switch to R1C1-style addressing, select Excel Options from the File menu. In the Formulas category, select the R1C1 reference style check box (see Picture 1)

Picture 1: Switching Excel to display R1C1 Style Reference

 

MS - Excel 2003:

1. On the Tools Menu, click Options.
2. Click General Tab.
3. Under Settings, click the R1C1 reference style check box.
4. Click Ok.

Picture 2

A1 Reference Style vs. R1C1 Reference Style

The A1 Reference Style

By default, MS-Excel uses the A1 reference style, which refers to columns as letters and refers to rows as numbers.

Columns:

• In Excel 2003, A through IV, for a total of 256 columns; and
• In Excel 2007/2010, A through XFD, for a total of 16384 columns

Rows:

• In Excel 2003, 1 through 65,536 Rows; and
• In Excel 2007/2010, 1 through 1048576 Rows.

The R1C1 Reference Style

In the R1C1 style, MS-Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

Using R1C1 with Relative References

Imagine you are entering a formula in a cell. To point to a cell in a formula, you use the letters R and C. After each letter, enter the number of rows or columns in square brackets.

The following list explains the “rules” for using R1C1 relative references:

1. For columns, a positive number means to move to the right a certain number of columns, and a negative number means to move to the left a certain number of columns. From cell E5, use RC[1] to refer to F5 and RC[-1] to refer to D5.
2. For rows, a positive number means to move down the spread sheet a certain number of rows. A negative number means to move toward the top of the spread sheet a certain number of rows. From cell E5, use R[1]C to refer to E6 and use cell R[-1]C to refer to E4
3. If you leave off the square brackets for either the R or the C, it means that you are pointing to a cell in the same row or column as the cell with the formula.
4. If you enter =R[-1]C[-1] in cell E5, you are referring to a cell one row up and one column to the left. This would be cell D4.
5. If you enter =RC[1] in cell E5, you are referring to a cell in the same row, but one column to the right. This would be cell F5.
6. If you enter =RC in cell E5, you are referring to a cell in the same row and column, which is cell E5 itself. You would generally never do this because it would create a circular reference.

Picture 3 shows how you would enter a reference in cell E5 to point to various cells around E5. 

Picture 3

Using R1C1 with Absolute References

An absolute reference is one where the row and column remain fixed when the formula is copied to a new location. In A1-style notation, Excel uses a $ before the row number or column letter to keep that row or column absolute as the formula is copied. To always refer to an absolute row or column number, just leave off the square brackets. This reference refers to cell $B$3 no matter where it is entered: 

=R3C2