Do 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.
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.
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”
- 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
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.
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
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
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.
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.
Now, let’s come to relative reference.
Using R1C1 with relative cell reference is simple like you use in A1 style.
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.
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.
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.
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
- Sort Columns In Excel
- Select Non-Adjacent Cells In Excel
- Insert your frequently used excel Functions
- Watch Window In Excel