# Excel worksheet R1C1 reference style

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 Formula Refer to the cell 1 row up and same column

Excel VBA Range Property FormulaR1C1

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist