Using A1 or R1C1 Reference Notation in Excel (& How to Change These)

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!

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.

A1 Cell in Excel

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.

A cell referring to cell A1 in Excel

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.

R1C1 cell

Similarly, R2C3 would refer to the cell in the second row and third column.

R2C3 cell in Excel

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)

Referring to R5C4 cell using R1C1 reference notation

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.

Referring to cell D5 from A1 in R1C1 notation

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).

Referring to cell D6 from A2 in R1C1 notation

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.

Referring to R5C4 cell using R1C1 reference notation
An example of Absolute R1C1 reference notation

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 Excel

How 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:

  1. Click the File tab in the ribbon
  2. Click on Options.
  3. In the Excel Options dialog box, click on Formulas
Select Formulas in the Excel options dialog box
  1. Within the ‘Working with Formulas’ section, check the ‘R1C1 reference style’ option
Check the R1C1 reference Style option
  1. 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 CellA1 Reference StyleR1C1 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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster