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.

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 business analysis, project management, and also 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 in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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