In Excel, there are two kinds of cell reference styles, first is A1 and second is R1C1.
Most of the Excel users don’t even know about the existence of R1C1 reference style. But, some users love to use it and found it more convenient than A1 reference style.
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 few years, Microsoft started to use A1 style so that people who migrated from Lotus would find it familiar, but R1C1 has been always there in Excel.
There are few additional benefits in R1C1 than A1.
So today, in this post, I’d like to share with you all the aspects of using R1C1 reference style.
Difference from A1 Style
In A1 reference style, you have column name as an alphabet and row name as a number. 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, that means row 1 and column 1 and if you go to A2 then it will be R2C1.
In above two examples, you have the same active cell, but different cell addresses.
But 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 RC.
Now, just think this way.
Cell B5 is 4 rows down and 1 column ahead from cell A1, so that’s why its address is RC.
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 R1C1 reference style, there is no permanent address for a cell (if you are using relative reference), a cell’s address dependents ob from where you are referring it.
Using R1C1 reference is a realistic approach to work with cell references. Ahead of this post, I will detail you about this approach.
But first of all, let’s learn to activate it.
How to Activate it
You can use one of the methods from below.
1. From Excel Options
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.
2. VBA Code
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Application.ReferenceStyle = xlA1
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 that in R1C1, you have row number and column number.
But, to understand how it works you need to learn all the different kind of references, just you have in A1 style.
A1 Reference Style
R1C1 Reference Style
Relative Row and Absolute Column
Absolute Row and Relative Column
Just like A1, you can use four different kind of references in R1C1 as mentioned in 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, when you refer to a cell, it will create the address of referred cell using it’s distance from the active cell.
If you want to go to a column in right side of the active cell, the number will be positive or if left side then a negative number.
And, same for the row positve number for below and negative for above active row.
In the above example, we 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 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 which is 2 columns before.
2. Absolute R1C1 Reference
Once crazy thing you may be noticed in above examples that row and column numbers have square brackets.
Let me show you what happens when you don’t use square brackets in cell reference.
In above example, when you are using row 2 and column 1 without square brackets it refers to the cell which is exactly in row 2 and column 1.
So the point is:
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 above example, we have multiplied cell R2C3 which is in 2nd row and 3rd column from R1C1 (cell A1) with a cell which is 1 column before from active cell.
R2C3 is without square brackets and when we drag down the formula, cell reference doesn't changes with it.
3. Semi Absolute/Relative R1C1
This is the one of the most used reference style in Excel in which you can make only column or row as an absolute or relative.
Let me show you an example.
In above example, you have multiplied the value in the first row with the value in the first column. This formula (=RC1*R1C) is working something like this.
- RC1 – When you skip specifying a row number then Excel treat the active cell’s row for reference. So here column will remain same but row will change once you drag the formula in all the cells.
- R1C – When you skip specifying a column number then Excel treat the active cell’s column for reference. So here row will remain same but 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 has 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 a 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 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 same in each total cell.
In all the ten cells, we have below formula.
It says, “sum the range of values in 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 cell.
Tip from Excel 2016 All-in-One For Dummies
You can use the R1C1 notation to check that you’ve copied all the formulasin 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 column of the table should be identical when displayed on the Formula bar as you make their cells current.
Here is an interesting thing:
When you record a macro, you can see that Excel use R1C1 formula reference in it while referring to cells and ranges.
And, if you know how to use R1C1 notion, you easily edit the recorded macro codes and save your 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 which are at left side of the active cell.
The code will be:
Selection.FormulaR1C1 = "=RC[-2]*RC[-1]"
Here with this code, you are multiplying the cell which is one column left with a cell which is two column 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 change 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 Offset property). Get more insight into Formula.R1C1 method from Jorge's guide.
While understanding the R1C1 you will feel some fear, but once you overcome this, you will 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, absolute, and semi relative cell reference.
Here are some points which we have covered in this tutorial and which you need to keep in mind.
- In R1C1, R stands for row and C stand for a column.
- To refer a row which is below and column which is ahead of the active cell you can use a positive number.
- To refer to a row which is above and column which is behind of 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 is 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?
Share your views in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.
Must Read Next
- 5-Popular Ways to Concatenate a Range of Cells in Excel
- 5 Custom Date Formats You Have To Learn Today Right Now
- Unlock 3x Formula Power with Excel 3D Reference for Cells and Ranges
- 5 Simplified Steps To Sort Horizontally in Excel [Columns Sorting]
- The Power of 3 Excel Wildcard Characters - Asterisk, Question Mark + Tilde