Most Excel users, including many advanced Excel users, have no idea what the R1C1 and A1 notation means in Excel.
In almost all of the situations, you don’t need to know what this is, and the default settings in Excel would be enough for you.
But since you are here reading this article, I’m assuming that you either have a need to know what is the difference between the R1C1 and A1 reference notations, or you’re naturally curious.
In this article, I will tell you everything you need to know about R1C1 and A1 reference notation in Excel, and the Pros and Cons of each.
So, let’s get to it!
This Tutorial Covers:
What is A1 Reference Style in Excel?
The A1 reference style is the default reference style notation in Excel, and if you have used Excel even for a few hours, I’m assuming you have already used it.
In layman’s terms, a reference style is a style you use to refer to the cells in Excel.
When using the A1 reference style, you would refer to any sale by first specifying the column alphabet/letter for that cell, followed by the row number for that cell.
For example, if you want to refer to the top-left cell in a worksheet in Excel, you would use A1 – where A tells us that it’s in column A, and 1 tells us that it’s in the first row.
As I mentioned, A1 is the default reference style notation in Excel. so if you enter and equal to sign in a cell in the worksheet, and then select any cell, you would automatically see the reference of that cell appear in the active cell.
What is R1C1 Reference Style in Excel?
R1C1 is the other type of reference style that you can use in Excel.
Here, R refers to the Row and C refers to the column, so R1C1 would refer to the cell in the first row and first column.
Similarly, R2C3 would refer to the cell in the second row and third column.
This is also known as the Relative Notation as the cell reference uses a row number and column number that tells you the relative position of that reference from the active cell.
For example, if I am in the top left in the worksheet (which is cell A1 or R1C1), and I want to refer to cell D5, then in R1C1 notation, the reference would be R5C4 (as D5 would be the cell in the fifth row and fourth column)
Just like the A1 notation, where you have absolute and relative references, you can also have these in the R1C1 notation.
Relative References in R1C1 Reference Style
Before I talk about the absolute cell reference in R1C1 notation, let me first talk about the relative reference.
If I’m in cell A1 (the top-left cell in the worksheet), and I want to refer to cell D5, below is the relative R1C1 reference I would have to use:
=R[4]C[3]
The above notation indicates that from the active cell I need to refer to the cell which is 4 rows below the active cell and 3 columns to the right of the active cell.
So if I am using this formula in cell A1, then this would refer to cell D5.
And the reason why we call this a relative reference is to use the same formula in cell A2, then it would refer to cell D6 (which is again 5 rows below and 4 columns to the right).
In a nutshell, the number in the square bracket is the offset value to consider. So R[5] means that we need to offset the row number by 5 and consider the row number that is five rows below the active cell, and similarly, for the column, C[4] offsets the column number by 4 so we need to refer to the column number that is four columns to the right.
One useful feature of using the R1C1 relative reference notation is that you can also use negative numbers in these square brackets.
So if you use =R[-1]C[-1], it would refer to the cell one row above and one column to the left.
Absolute References in R1C1 Reference Style
Absolute reference in the R1C1 notation is quite straightforward.
You don’t use the square brackets in the absolute reference, and the reference you specify is the one that is actually used (so there is no offsetting).
For example, if I need to refer to cell D5, I will use R5C4.
No matter where in my worksheet I use this reference R5C4, it would always refer to cell D5.
Hence it’s called absolute (as it doesn’t change).
Unlike relative references, you cannot use negative numbers in Absolute Reference. You also can not use 0. The number should always be a positive whole number.
Also read: Absolute, Relative, and Mixed Cell References in ExcelHow to Switch from A1 to R1C1 Notation (or R1C1 to A1)?
As I mentioned, by default Excel has the A1 reference style enabled. but you can easily switch between the two reference styles with a few clicks.
Below are the steps to switch from the A1 reference style to the R1C1 reference style:
- Click the File tab in the ribbon
- Click on Options.
- In the Excel Options dialog box, click on Formulas
- Within the ‘Working with Formulas’ section, check the ‘R1C1 reference style’ option
- Click Ok
The above steps would enable the R1C1 reference notation. in case you already have some formulas in the worksheet, you’ll notice that the reference style would now be shown in the R1C1 format.
Similarly, if you want to switch the reference style back to A1, follow the same steps and uncheck the ‘R1C1 reference style’ option in Step 4.
When you change the reference style from A1 to R1C1, you will notice that the row and column headers now both show numbers (while earlier the column headers had letters)
A1 vs R1C1 Notation Comparison
Below I have a table where I have compared the two reference styles notations:
| Formula in Cell | A1 Reference Style | R1C1 Reference Style |
|---|---|---|
| A1 | =A2 | =R[1]C |
| A1 | =$A$2 | =R2C1 |
| A3 | =A1+A2 | =R[-2]C+R[-1]C |
| A3 | =$A$1+$A$2 | =R1C1+R2C1 |
| A4 | =SUM(A1:A3) | =SUM(R[-4]C:R[-2]C) |
Which Reference Style Should You Be Using?
Unless you have a reason to use the R1C1 reference style notation, I would recommend sticking to the default A1 reference style.
A1 style it’s easier to use, And since almost all the Excel users use it, it makes it easier for you to share your work with other people.
One instance where you may want to use the R1C1 notation is when you want to debug formulas and identify errors.
But it’s still a good idea to understand how both these notations work. While you may not have much use of the R1C1 notation in the worksheet, an understanding of how these work can be useful if you work often with VBA coding.
I hope you found this excel tutorial useful.
Other excel tutorials you may also like:
- How to Find Circular Reference in Excel
- How to Reference Another Sheet or Workbook in Excel (with Examples)
- #REF! Error in Excel – How to Fix the Reference Error!
- How to Copy and Paste Formulas in Excel without Changing Cell References
- How to Find External Links and References in Excel
- How to Return Cell Address Instead of Value in Excel
