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

Most Excel users, including many seasoned Excel users, are unaware of what the R1C1 and A1 notations in Excel signify.

You don’t need to know this in virtually all circumstances; Excel’s default settings will function just fine.

However, given that you are reading this, I’m going to assume that either you need to know what the distinction between R1C1 and A1 reference notations is or that you are just naturally inquisitive.

In this post, I’ll cover all you need to know about Excel’s R1C1 and A1 reference notations, as well as their advantages and disadvantages.

Let’s start, now!

This instruction explains:

1. What does Excel's A1 Reference Style mean?
2. What does Excel's R1C1 Reference Style mean?
2.1 R1C1 Reference Style Relative References
2.2 R1C1 Reference Style Absolute References
3. How can I change the notation from A1 to R1C1 or vice versa?
4. Comparing the Notations A1 with R1C1
5. Which reference format need you to be employing?

1. What does Excel’s A1 Reference Style mean?

I’m going to assume that if you have used Excel for even a short period, you have already used the A1 reference style, which is the default reference style notation.

In plain English, a reference style is a style you employ in Excel to refer to the cells.

If you were to refer to any sale using the A1 reference style, you would first supply the column alphabet or letter for that cell, then the row number for that cell.

For instance, in Excel, you might use A1 to refer to the top-left cell of a worksheet, where A indicates that the cell is in column A and 1 indicates that the cell is in the first row.

As I previously stated, Excel’s default reference style notation is A1. Therefore, if you insert an equal sign in a worksheet cell and then select any other cell, the reference to that cell will immediately appear in the active cell.

2. What does Excel’s R1C1 Reference Style mean?

The alternative reference style available in Excel is R1C1.

R1C1 refers to the cell in the first row and the first column since R stands for row and C for the column in this context.

The cells in the second row and third column would be similarly denoted as R2C3.

As the cell reference employs a row number and column number to indicate the reference’s relative position to the current cell, this is also known as Relative Notation.

For instance, if I wish to refer to cell D5 and I am in the worksheet’s upper left corner (cell A1 or R1C1), I would write R5C4 in R1C1 notation (as D5 would be the cell in the fifth row and fourth column)

You can have absolute and relative references in the R1C1 notation, just like you can with the A1 notation.

also, Read Excel Tutorial Step by Step

2.1 R1C1 Reference Style Relative References

Let’s first discuss the relative reference in R1C1 notation before moving on to the absolute cell reference.

The relative R1C1 reference I would have to use if I were in cell A1 (the top-left cell in the worksheet) and I wanted to refer to cell D5 is listed below:

=R[4]C[3]

According to the notation above, I should refer to the cell that is 4 rows and 3 columns below and to the right of the active cell from the active cell.

Therefore, if I were to use this formula in cell A1, cell D5 would be the reference.

To apply the same formula in cell A2, it would then refer to cell D6, which is why we refer to this as a relative reference (which is again 5 rows below and 4 columns to the right).

In a nutshell, the offset value to take into account is the one in the square bracket.
Since R[5] offsets the row number by 5, we must refer to the row number that is five rows below the active cell.
Similarly, C[4] does the same for the column, so we must use the column number that is four columns to the right.

The R1C1 relative reference notation has the advantage of allowing the usage of negative values inside these square brackets.

As a result, the cell one row above and one column to the left would be referenced if you used =R[-1]C[-1].

2.2 R1C1 Reference Style Absolute References:

In the R1C1 notation, absolute reference is quite simple.

The absolute reference is written without square brackets, and the reference you indicate is the one that is utilized (so there is no offsetting).

For instance, I would use R5C4 to refer to cell D5.

If I use this reference R5C4 elsewhere on my spreadsheet, it will always refer to cell D5.

Because it doesn’t change, this is why it is termed absolute.

Negative integers are not allowed in absolute references, unlike relative references. 
Additionally, you cannot use 0. The sum must always be a positive whole number.

also, Read Excel Tutorial Step by Step

MS Excel VBA 

3. How can I change the notation from A1 to R1C1 or vice versa?

As I previously stated, Excel has the A1 reference style enabled by default. however, it only takes a few clicks to move between the two reference styles.

The procedures to change from the A1 reference style to the R1C1 reference style are as follows:

1. On the ribbon, select the File tab.
2. Select Options.
3. Click Formulas in the Excel Options dialogue box.
4. Check the “R1C1 reference style” box under the “Working with Formulas” section.
5. Press “OK”

The R1C1 reference notation is enabled by the aforementioned actions. If the worksheet already has any formulae, you’ll note that the reference style is now shown in the R1C1 format.

The same procedures must be followed, except in Step 4 deselect “R1C1 reference style” to change the reference style back to A1.

The row and column headings now also display numbers when you switch the reference style from A1 to R1C1 (while earlier the column headers had letters)

4. Comparing the Notations A1 with R1C1

I’ve compared the two reference styles notations in the table below:

5. Which reference format need you to be employing?

I advise keeping with the standard A1 reference style unless you have a good reason to adopt the R1C1 reference style notation.

The A1 style is simpler to use and makes it simpler for you to share your work with others because practically all Excel users use it.

When you wish to troubleshoot calculations and find problems, you might want to utilize the R1C1 notation.

However, it’s still a good idea to be familiar with how both of these notations operate. Even while you might not use the R1C1 notation very often on the worksheet, knowing how they operate might be helpful if you frequently utilise VBA code.

I hope this Excel tutorial was helpful.

MS Excel VBA