How to Use R1C1 Reference Style in Excel?

In Microsoft Excel, we have two different types of reference styles. By default, the reference style that everybody uses is the A1 reference style while another referencing style is the R1C1 reference style. The R1C1 reference style is particularly useful in the VBA programming language.

So let us see how it works.

A1 Reference Style and R1C1 Reference Style

In A1 Reference Style, the columns are represented using alphabets, and the Rows are represented using Numbers. When you select cell B2, you are in Column B and Row 2.

In R1C1 Reference Style in Excel, the columns and rows are represented with the help of numbers. When you select cell B2, it will be R2C2.

Activate R1C1 Reference Style in Excel

To activate the R1C1 reference style, there are two methods.

• Go to the File Tab and click on Options.
• Go to the Formulas tab and mark the checkbox for R1C1 reference style.

Alternatively, you can use the following Macro and run it. This will apply the R1C1 reference style to all the workbooks. If the R1C1 reference style is already selected then it will set the reference style back to A1.

Sub ChangeCellRef()
'if the reference style is A1 then set it to R1C1
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else
'if reference style is R1C1 then set it to A1
Application.ReferenceStyle = xlA1
End If
End Sub

Here we have used the Application.ReferenceStyle property to change the value of reference style.

When you activate the R1C1 reference style, all the formulas are updated to the R1C1 referencing and are changed.

Working With R1C1 Reference Style

There are three different types of referencing used in the R1C1 reference style just like the A1 reference style. These are:

• Relative Referencing
• Absolute Referencing
• Mixed Referencing

These references are easy to understand.

Relative Referencing in R1C1 Reference Style

For using Relative Referencing, both the column number and row number are enclosed in square brackets. The row number and column number can be positive or negative depending on the direction of movement from the active cell.

• Column Number
  • Positive Value – when we move to the right of the active cell.
  • Negative Value – when we move to the left of the active cell.
• Row Number
  • Positive Value – When we move down the active cell.
  • Negative Value – When we move above the active cell.

When we are using Relative Referencing, no cell has a fixed address. It depends on the number of rows and columns we need to jump from the active cell to reach the referred cell.

For instance, let us say the active cell is cell A1. We wish to move three columns to the right of A1 and five rows down to A1. This will refer to cell D6.

Similarly, you can move in different directions from the active cell to refer to different cells.

If the row or column is unchanged then we will not mention the value in the R1C1 formula.

Absolute Referencing in R1C1 Reference Style

When we do not use square brackets in the R1C1 reference Style then it becomes Absolute Referencing. In Absolute referencing, every cell has a fixed address corresponding to the column number and row number irrespective of the position of the active cell.

For instance, the following are the cell references in A1 Reference Style and in R1C1 Reference Style (Absolute Referencing).

The thing is when you do not use square brackets, then Excel will treat it as Absolute Referencing.

Mixed Referencing in R1C1 Referencing Style

As the name suggests, in Mixed Referencing we use both Relative and Absolute Referencing for columns and rows.

It has two types.

• Locked Column – The Column number is locked using Absolute Referencing. The Row number is in Relative Referencing.
• Locked Row – The Row number is locked using Absolute Referencing. The Column number is in Relative Referencing.

For instance, the following are examples of cell references using Mixed referring using the R1C1 Reference Style.

This might seem confusing at first but it is not that hard to understand once you get used to it.

This brings us to an end.

Thank you for reading.❤