This Excel tutorial explains how to use R1C1 reference style in Excel worksheet.
Excel worksheet R1C1 reference style
In R1C1, “R” refers to row, while “C” refers to column. Formula R1C1 allows you set formula for a cell, using relative row and column.
For example, you want to set a formula in C2 to sum the two cells on the left, before that you should ask yourself a question:
Do I want to sum the cell on left 1 and left 2 relative to C2, or
Do I just want to sum A2 and B2, no matter how column number is changed?
This question matters because if you insert a column between A and B, old B2 would become C2, so do you still want A2+B2 or A2+C2?
If your answer is really A2+B2 no matter what, type =A2+B2 and then press F4 to add $ (absolute value)
=$A$2+$B$2
If your answer is the A2+C2, then you need R1C1 style, where you specify the relative position of the cells you want to sum. By default, when you type a formula in C2
=A2+B2
Excel will automatically transform your formula to
=RC[-2]+RC[-1] (but your screen will only show =A2+B2)
That is why you can insert column or row but the reference cells also change accordingly. You can prove this by going to Options > Formulas > R1C1 Reference Style
Now you can see what is the underlying R1C1 formula. You can see the column header also change to number.
Syntax
Your must enable “R1C1 reference style” before you can type in R1C1 style.
=R[x]C[y]
x and y are the coordinate relative to the formula Cell
where x is the number of rows right to formula Cell (negative value means left to the formula Cell)
where y is the column down from the formula Cell (negative value means up from the formula Cell)
you can skip [x] and [y] if the value is 0
Example of FormulaR1C1
Type the formula in C2
| Formula | Return | Explanation |
| =R[-1]C[-2] | Field 1 | Refer to the cell 1 row up and 2 columns left |
| =R[-1]C or =R[-1]C[0] | Formula | Refer to the cell 1 row up and same column |
Recommended Readings
Excel VBA Range Property FormulaR1C1
