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