excel r1c1 reference style using ultimate guide by excel champsDo you know R1C1 Reference Style in excel is one of the unused features?

Most of the excel users don’t even know about the existence of R1C1 reference style.

But, some super users love to use it.

They found it more convenient than a1 reference style.

This post will help you to master R1C1 reference style. And, also inspire you to use it with full power.

Download this Quick PDF Guide to learn about using R1C1 reference style.

Quick Intro

By default, we have A1 reference style in excel which you are using right now. But, excel has another reference style which can you use in your worksheet.

In A1 reference style, we have column name as an alphabet & row name as a number. When we select A1 cell that means we are in column A & row 1.Difference Between A1 Reference Style & R1C1 Reference Style
In above two example, we have the same active cell.

But in both example, there are two differences.

Can you spot?

Yes, you are right.

  • In A1 reference style, the column name is in Alphabet & In R1C1 reference style column name is a number.
  • In A1 reference style, the address of the cell is A1 & In R1C1 reference style address of the cell is R1C1.

These are the two difference you will found when you use R1C1 reference style in excel worksheet.

But, before I show you how it works & what are the benefits of using this R1C1 notation for cell reference.

Let me show you first that how can you activate it in you the worksheet.

Activate R1C1 Reference Style

Please follow these simple steps to set R1C1 reference as default.

  • Go to File Tab → Option → Formulas → Working with formulas.
  • Tick mark “R1C1 Reference Style”

Activate R1C1 Reference Style In Excel

  • Click OK.

And, if you are a macro savvy, you can use the below macro code to toggle between cell reference styles in excel

Sub ChangeCellRef()
If Application.ReferenceStyle = xlA1 Then
    Application.ReferenceStyle = xlR1C1
Else
    Application.ReferenceStyle = xlA1
End If
End Sub

Once you make R1C1 your default reference style, all the references in your workbooks will change.

Using R1C1 Reference Style

Using R1C1 reference style is a realistic approach to work with cell references.

Ahead of this post, I will detail you about this approach.

Basic Functionality of R1C1 Notation

Basic Functionality of R1C1 Notation in Cell Reference

By using OFFSET function in excel you know that you can refer to a cell which is at a specified number of rows and columns from the active cell.

Here in this cell reference style, we have the same approach for that.

Refer Cell With R1C1 Reference Style In Excel

In above example, active cell is on 4th row & 2nd column & I am referring to a cell which is 1 column before & 2 rows above to it.

Now, one thing is clear that you can refer to a cell or range by its distance from another cell.

Once crazy thing you have noticed here is that row & column number have square brackets.

Let me show you what happens when I don’t use square brackets in cell reference

Refer Cell With R1C1 Reference Style In Excel Without Brackets

In above example, when I am using row 2 & column 1 without square brackets & referred cell is exactly in row 2 & column 1.

When you are not using square brackets in your cell reference, it will locate the cell by using a1 as a base.

Absolute & Relative References

Both absolute & relative references are important in excel.

So, let me show you how to use these both type of references with R1C1 notation.

Let’s say you are in cell B5 & you want to refer to the cell A1.

You have following options to make your reference.

A1 Reference Style               R1C1 Reference Style

A1                                                R[-4]C[-1]

$A$1                                           R1C1

$A1                                              R[-4]C1

A$1                                              R1C[-1]

Absolute Reference

When you want to use the absolute reference you can refer the cell by using R1C1 (cell A1) as the starting point.

Let me show you with an example.

Use Absolute Cell Reference With R1C1 Reference Style In Excel Without Brackets

In above example, I have multiplied cell R2C3 which is at 2 rows & 3 columns from R1C1 (cell A1) with a cell which is 1 column before the R5C3.

R2C3 is without square brackets & when I am dragging down the formula cell reference is not changing with it.

Relative Reference

Now, let’s come to relative reference.

Using R1C1 with relative cell reference is simple like you use in A1 style.

Use Relative Cell Reference With R1C1 Reference Style In Excel Without Brackets

In above example, I have used the relative reference to multiply a cell which is 1 column before the cell with a cell which is 2 columns before.

If you copy & paste this formula to any other cell in your worksheet it will always multiply the cell which is 1 column before the cell with a cell which is 2 columns before.

Semi Relative Reference or Semi Absolute Reference

This is the most used reference style in which you make the only column or row as an absolute or relative.

Let me show you an example to do it with R1C1 cell reference Style.

Use Semi Relative Cell Reference With R1C1 Reference Style In Excel Without Brackets

In above example, I have multiply the value in the first row with the value in the first column. This formula (=RC1*R1C)  is working something like this.

RC1 – Column will remain same but row will change.

R1C – Column will change but row will remain same.

So these are the different cell reference styles in which you can apply by using R1C1 notation in excel.

While you are learning cell reference R1C1 for the first time, it will be some confusion in your mind to go with it or not.

If you trust me, you’ll found this style handy & useful once you get you hands dirty with it.

But the best part is yet to come, you can use it with VBA & Macros as well.

R1C1 Reference Style With VBA

When it comes to VBA, R1C1 work like a magic.

If you ever record macros you will find cell reference with R1C1. And, If you know how to use R1C1 reference in VBA you change easily make changes in the recorded macro.

And, If you know how to use R1C1 reference in VBA you change easily make changes in the recorded macro.

Let me clarify with an example.

Use R1C1 Reference Style In Excel VBA

In above example, I have multiplied the values of column 2 with the values of column 3.

This macro code will multiply the cell which is 1 column before the cell with the column which is 2 column before the cell.

Now the best part of this is when you change the location of your table it will work in the same pattern.

You don’t have to make a change in your code. You just have to select the range & it will multiply the values.

Last Words

I always found R1C1 reference style more realistic to use.

While understanding the R1C1 you will feel some fear but once you overcome this, you have a new flexible approach to work in excel.

One thing which I just forgot to tell you that you can use F4 to switch between Relative, Absolutes & Semi Relative Cell Reference.

Over To You

What do you think about it?

Have you ever get into the R1C1 reference style or you are hearing about this first time?

Don’t any of your friends tell you about this?

But, you can tell them, just share this post & let’s check that they know about this or not.

And, please don’t forget to share your view in the comment box.

Move To Next Tutorials