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.
=R[10]C
in C10 refers to C20. You can modify both the row

and column. For example,
=R[1]C[1]
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.