In Excel, there are two kinds of cell reference styles first is A1 and the second is R1C1. Well, most Excel users don’t even know about the existence of the R1C1 reference style.
But some users love to use it and found it more convenient than A1. The R1C1 style is a kind of old one.
It was first introduced in Multiplan which was developed by Microsoft for Apple Macintosh.
But after a few years, Microsoft started to use the A1 style so that people who migrated from Lotus would find it familiar, but R1C1 has been always there in Excel.
There are fewer additional benefits in R1C1 than in A1. And today in this post, I’d like to share with you all the aspects of using the R1C1 reference style.
So without any further ado, let’s explore this thing.
Difference Between A1 and R1C Reference Style
In the A1 reference style, you have the column name as an alphabet and the row name as a number and when you select the A1 cell that means you are in column A and row 1.
But in R1C1 both column and row are in numbers.
So, when you select cell A1 it shows you R1C1, which means row 1 and column 1, and if you go to A2 then it will be R2C1.
In the above two examples, you have the same active cell, but different cell addresses. The real difference comes when you write formulas and use a reference to other cells.
In R1C1, when you refer to a cell it creates the address of referred cell using its distance from the active cell.
For example, if you refer to cell B5 from cell A1 it will show the address of B5 as R[4]C[1].
Now, just think this way. Cell B5 is 4 rows down and 1 column ahead of cell A1, so that’s why its address is R[4]C[1].
But here’s the kicker. If you refer to the same cell from a different cell then its address will be different.
The point to understand is, In the R1C1 reference style, there is no permanent address for a cell (if you are using relative reference), so a cell’s address dependents ob from where you are referring to it.
Using the R1C1 reference is a realistic approach to working with cell references.
Ahead of this post, I will detail this approach. But first of all, let’s learn to activate it.
How to Activate R1C1 Cell Reference in Excel – Simple Steps
To use R1C1, the first thing you need to do is to activate it and for this, you can use any of the below methods.
From Excel Options
Please follow these simple steps to set the R1C1 reference as default.
- Go to File Tab ➜ Option ➜ Formulas ➜ Working with formulas.
- Tick mark “R1C1 Reference Style”.
- Click OK.
Using a VBA Code
If you are macro savvy and want to save time then you can use the below macro code to toggle between cell reference styles.
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 formulas in all the workbooks will change.
How R1C1 Reference Style Works
Now at this point, you are clear about one thing in R1C1, you have row number and column number.
But to understand how it works, you need to learn all the different kinds of references.
A1 Reference Style | R1C1 Reference Style | Reference Type |
---|---|---|
A1 | R[-4]C[-1] | Relative Reference |
$A$1 | R1C1 | Absolute Reference |
$A1 | R[-4]C1 | Relative Row and Absolute Column |
A$1 | R1C[-1] | Absolute Row and Relative Column |
Just like A1, you can use four different kinds of references in R1C1 as mentioned in the above table. But now, let’s dig deeper into each type of reference.
1. Relative R1C1 Reference
Using relative reference in R1C1 is quite simple. In this reference style, when you refer to a cell, it creates the address of the referred cell using its distance from the active cell.
If you want to go to a column on the right side of the active cell, the number will be positive, or if the left side then a negative number. And same for the row, a positive number for the below and a negative for the above active row.
In the above example, we have used the relative reference to multiply a cell that is 1 column before the cell with a cell that is 2 columns before.
If you copy and paste this formula to any other cell in your worksheet it will always multiply the cell which is 1 column before the active cell with a cell that are 2 columns before.
2. Absolute R1C1 Reference
I’m sure you have noticed this thing in the above example row and column numbers have square brackets. Let me show you what happens when you don’t use square brackets in the cell reference.
In the above example, when you are using row 2 and column 1 without square brackets this means that the cell you are referring to is exactly in row 2 and column 1.
Here’s the real thing: In R1C1, when you want to use the absolute reference you can skip using square brackets and Excel will treat cell R1C1 (cell A1) as the starting point.
Let me show you an example.
In the above example, we have multiplied cell R2C3 which is in the 2nd row, and the 3rd column from R1C1 (cell A1) with a cell that is 1 column before from the active cell.
R2C3 is without square brackets and when we drag down the formula, the cell reference doesn’t change with it.
3. Semi Absolute/Relative R1C1
This is one of the most used reference styles where you can make the only column or row, absolute or relative. Let me show you an example.
In the above example, we have multiplied the value in the first row by the value in the first column.
- RC1: When you skip specifying the row number then Excel treats the active cell’s row for reference. So here column will remain the same but the row will change once you drag the formula in all the cells.
- R1C: When you skip specifying the column number then Excel treats the active cell’s column for reference. So here row will remain the same but the column will change once you drag the formula in all the cells.
Using R1C1 Reference in Formulas
Once you switch from A1 to R1C1 style, you will see all the formulas in your worksheet have changed.
Might be the first time you find them confusing, but in reality, they are quite simple. Let me show you how this thing works with simple sum formula.
In the below example, we have a data table with monthly sales of 10 products with a total column at the end.
And here you are using the A1 reference style.
In this reference style, each total cell has a different formula using a different cell reference.
But as soon as, you change the reference from A1 to R1C1, all the formulas have changed and they are the same in each total cell.
In all ten cells, we have the below formula.
=SUM(RC[-12]:RC[-1])
This formula says: “Sum the range of values from the cell 12 columns to the left (C[–12]) in the same row through the cell that is one column to the left (C[–1]) in the same row.“
Because in all the cells where we are calculating the total, the range of values is in the same order and that’s why we have the same formula for all the cells.
Tip from Excel 2016 All-in-One For Dummies: You can use the R1C1 notation to check that you’ve copied all the formulas in a spreadsheet table correctly. Move the cell cursor through all the cells with copied formulas in the table. When R1C1 notation is in effect, all copies of an original formula across an entire row or down an entire table column should be identical when displayed on the Formula bar as you make their cells current.
Using R1C1 Reference in VBA
When you record a macro, you can see that Excel uses the R1C1 formula to reference while referring to cells and ranges.
And, if you know how to use the R1C1 notion, you easily edit the recorded macro codes and save a ton of time. For this, you need to understand the working of the FormulaR1C1 method.
Let’s say you want to enter a formula in the active cell where you need to multiply two cells that are on the left side of the active cell.
Sub R1C1Style()
Selection.FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub
You are multiplying the cell which is one column left with a cell which is two columns left from the active 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 any changes in your code. But, what will happen if we use the A1 reference here instead of R1C1:
We can’t use this code for every cell because the cell reference would be fixed (Otherwise we need to use the Offset property). Get more insight into Formula.R1C1 method from Jorge’s guide.
Conclusion
While understanding the R1C1 you will feel some fear, but once you overcome this, you will have a new flexible approach to working in Excel.
One thing which I just forgot to tell you is that you can use F4 to switch between relative, absolute, and semi-relative cell references. Here are some points which we have covered in this tutorial and which you need to keep in mind.
- In R1C1, R stands for a row and C stand for a column.
- To refer to a row that is below and a column that is ahead of the active cell you can use a positive number.
- To refer to a row that is above and a column that is behind the active cell you can use a negative number.
- When you use a row or column number without a square bracket, Excel will treat it as an absolute reference.
- In VBA, to use R1C1 you need to use the FormulaR1C1 method.
I hope you have found this post helpful and now tell me one thing.
Will you start using R1C1?
Make sure to share your views with me in the comment section, I’d love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.
Related Formulas
- Back to the List of Excel Formulas
My code works fine as long as I have the brackets but I want hard referencing so when it gets copied down it goes back to the same row. When I remove the brackets I get a run time error ‘1004’ Application-defined or object-defined error.
Interesting – thanks! But, how can I slow down your video examples? They go too fast for me to follow.
Oops! which video you are talking about.
I have a query, I want to use Vlookup formula in a macro with R1C1 Reference Style, however my lookup table is on a different worksheet (but in the same workbook). How can R1C1 system handle this scenario ?
Thanks in advance !
I would like to reference a cell by its position exclusively. For example I want to reference cell A22 and if a row is inserted above row 22, I still want to reference A22, not A23.
Anyway to do that? I find that with both absolute and relative referencing all formulas referencing A22 read A23 after a row is inserted above row 22. Maybe complicating it is the fact the the referencing formula is on a different sheet.
Nevermind, I just figured it out. For anyone needing this, use the Indirect function as follows. To reference cell A22 in worksheet “Summary” enter the target address in a working CELL say A1 in the current worksheet: Summary!A22. This points to your target cell and because it’s just text, doesn’t get updated by any relative moves or inserts.
Then in the referencing CELL enter =INDIRECT(A1)
This builds a reference from the contents of A1 which then points to the actual target.
Thank you, Puneet, but I’d like to use a formula, specifically a RC formula, to determine the numbers of rows above the current row to reference. In this simplified form, I’d like to use the value in RC[-1] in an RC reference for the number of rows above the current row. It seems like =R[RC[-1]]C should work. Am I missing some key part of the syntax?
Pretty interesting. I have encountered R1C1 before and was totally confused, but newbie enough to still be used to that.
All the best 🙂
Even better than the macro code to change from A1 to R1C1, use the following code to have a button that toggles between the two styles (really handy to flick back to A1 style when someone is talking about what is in column BX, for instance)
Sub ChangeCellRef()
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
ElseIf Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
End If
End Sub
This is a really great explanation of R1C1. Thank you very much!