Excel worksheet R1C1 reference style

What is R1C1 reference style for worksheet?

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.

Why do you need R1C1 for formula?

excel_property_r1c1_03

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

excel_property_r1c1_02

Now you can see what is the underlying R1C1 formula. You can see the column header also change to number.

excel_property_r1c1_01

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

excel_property_r1c1_01

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

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

Leave a Reply

Your email address will not be published. Required fields are marked *