Microsoft Office Tutorials and References
In Depth Information
LEARN R1C1 REFERENCES
Note that I am not suggesting that you switch over to R1C1 references. That
would be as crazy as suggesting that Microsoft replace the familiar File, Edit,
View, Insert, Format, Tools, Data, Window, and Help with a new user interface.
Instead, I am suggesting that you understand the reference style because there
are times when it is easier to use R1C1 than A1 (as with INDIRECT or VBA).
Solution: An R1C1-style reference contains the letter R and the letter C. With-
out any modiﬁ ers, the R means “the same row where this formula is entered,”
and the C means “the same column where the formula is entered.”
The simplest R1C1 reference is =RC. If you are in cell C10 and enter =RC, you
are referring to cell C10. (This would also cause a circular reference error.)
When you follow the R or the C with a number in square brackets, you are re-
ferring to a cell that is some number of cells away. For example =RC[-1] in
C10 refers to B10. =RC in C10 refers to C20. You can modify both the row
and column. For example, =RC in C10 refers to D11.
Note: For rows, positive numbers move down the worksheet. Negative num-
bers move up the spreadsheet. For columns, positive numbers move to the
right on the worksheet. Negative numbers move to the left on the worksheet.
If you want to build a formula to calculate GP% in column I, you have to divide
this row’s column H by this row’s column F. This is a bit of a hassle to do, and
the formula is different in each row (Figure 47).
Figure 47. With A1 references, the formula in each cell is dif erent.
Think about the formula you use to do this in R1C1 style. You want this row,
one column to the left divided by this row, 3 columns to the left. You can use
exactly the same formula, no matter which row you are in: All the formulas in
Figure 48 are =RC[-1]/RC[-3] . This is pretty cool.