The standard way to reference cells is the A1 style. With this style, we use letters to reference a column, and a number to reference a row.
There is, however, another way to reference cells in Excel. This second option is called R1C1 cells referencing. This option allows us to reference both rows and columns by numbers.
In the example below, we will show how to switch to this referencing style and how to use it.
Switch to R1C1 Reference Style
There are two different options to switch on the R1C1 style of referencing cells. The first one is that you go to the File tab >> Options >> Working with formulas and then choose R1C1 reference style:
The other way in which we could do this is through VBA. We will open the VBA by clicking ALT + F11, then going to the Insert tab, and then choosing Module:
In the window that opens on the right side, we will insert the following code:
Sub CellReference ()
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Application.ReferenceStyle = xlA1
This simple code can be used to simply switch between cell references. We will also add the button from Form Controls that are located under Developer tab >> Controls >> Insert >> Form Controls >> Button to use the code easier:
This button can be inserted anywhere in our sheet, and we will attach our macro to it. This is what it will look like:
On the upper left side in the picture above, you can see that we are located in cell A1, which is now referred to as cell R1C1. If we would go on and click on our button, the original (A1) reference would be shown.
Use R1C1 References
Just as for A1 references, there are different types for R1C1 references. There is a total of four reference types:
- Relative reference
It is used when we want to get the value of one cell located in relation to another cell. It is written in the following form (example): R[-5]C.
For example, we will use a list of prices and discounts and will use relative referencing to calculate the price with the discount included. Our formula in cell R2C3 (where the first price with discount is calculated) will be:
From this formula, we can gather that the first number in the formula is in the same row, but two columns backward from column C (which will be column A). We use the same approach to figure out the rest of the numbers. Our formula looks like this in the table:
- Absolute reference
This is similar to the situation in which we use the F4 key when dealing with A1 referencing. We will still use the F4 key when dealing with R1C1 referencing, and when we want to use it, we simply write it without parentheses.
To show it in the example, we will define that the same discount is applied to every price in our list. We will put a 10 percent discount in column 4 (also notice that the columns are now presented in numbers, rather than in letters), and will calculate the prices with this percentage of discount in column 5. This is the formula that we will insert into cell R2C4:
And this is what it looks like in our sheet:
As seen in the formula above, the cell R2C4 is absolutely referenced, meaning that it is used throughout our list.
- Relative row and absolute column
In this scenario, we only want the number in the column to be absolute, but the number in the row section to be relative.
- Absolute row and relative column
This is the last case that we have, and it is pretty self-exploratory, with our row that is being referenced is absolute, and our column being relative.
R1C1 Referencing and VBA
We can use A1 referencing in VBA, but we can also use R1C1 in VBA. Usually, when people begin to work with VBA, they simply “record” their macros, and when they do this, the formulas that they use will be presented in this reference style.
For example, if we have two numbers in cells R12C2 and R13C2 (those are cells B12 and B13):
Now, to record the macro to automatically calculate the sum of these two numbers, we will go to the Developer tab, and then choose Record Macro under Code:
When we click on it, a window will appear that will ask for a Macro name and location in which we will store our macro:
We will click OK and do our action- a sum the two numbers:
We will then click on the Stop Recording button (now showing instead of the Recording button), and we will have the following code stored in our VBA module:
As seen, our macro is saved in R1C1 referencing when recorded.
Being familiar with this referencing style can help you in the initial stages when getting familiar with VBA.