fbpx

What are Reference Styles? How to Use R1C1 Reference Style in Excel

Every Excel Spreadsheet contains rows and columns. Most of the time, columns are known and represented by letters (A, B, C), and rows are known by numbers (1, 2, 3). While you’re in Excel, this is often called the A1 reference vogue. However, some are in favor to use a unique methodology where the columns are known by numbers. This is termed as the R1C1 reference style.
Considering the case below, the image on the left features a variety of numbers over every column, which implies its usage for the R1C1 reference style. Whereas, the image on the right side is utilizing the A1 reference style.

A1 Reference style R1C1 Reference style

While the R1C1 reference style is useful for sure things, you will likely wish to use the A1 reference style most of the time. This tutorial can use the A1 reference style. If you are presently utilizing the R1C1 reference style, you’ll have to turn it off

How to turn OFF or ON the R1C1 reference style:

  • Open File Tab
  • Click on the Options
  • Click on the Formulas
  • Uncheck to Turn OFF R1C1 reference style.
  • Check to Turn ON R1C1 reference style
  • Now, Click OK.

Below, the image shows how to turn off the R1C1 reference style.

Go to File , Options for reference style

Formula, R1C1 Reference style

Excel is now ready to use the A1 Reference style.

If you ever got dragged or strayed into the Options screens of MS Excel, you might have definitely observed one thing that is referred to as R1C1 reference style. You might have even tried it and after you saw that each of the columns had now changed. The columns are now present in the modified form i.e from letters to numbers, in the panic-stricken state everyone will switched it back.

The R1C1 style is encompassed with some major benefits over the quality present in the A1 style. However A1 Style will slightly take in your efforts before you get used to it!

So, is this the R1C1 style which is new? Well, not in any respect. You can refer to, this cell reference style dates that belong back to 1982. And can even focus on introduction of Multiplan. This actually acts as a rival to VisiCalc and Lotus 1-2-3.

Multiplan was introduced by Microsoft for Apple Macintosh and used this cell referencing style rather than using what was the best fit to compete with the standard in spreadsheets – A1 style.

In the succeeding few years, Lotus 1-2-3 became “the” spreadsheet package. So, in order for Microsoft to steal Lotus users who were accustomed to use the A1 style. They included the A1 style in MS Excel so that the folks that migrated across would realize about its similarity. Well, due to the industry standards for Spreadsheets, Excel eventually overtook Lotus, in the year 1993. As the reminder of what they’ve always claimed is History.

But enough of the history lesson…how does R1C1 differ to A1?

The most obvious difference is the column references. Instead of letters, you get numbers.

Period! It was enough of the history lesson…how does R1C1 differ from A1?

The most obvious distinction is that of the column references. Well, rather than letters you obtain numbers.

The real difference comes when you want to write formulas. Let’s take a simple example adding two columns together. I am displaying the formulas so the differences are clearer.

First, the familiar A1 style:

difference in formula for A1 reference style

And now for R1C1….

difference in formula for A1 reference style

At first this appears to look quite horrific and 10 times additionally difficult than that of the A1 style. Before I break down about the steps of how it works, what does one notice between the 2 styles (other than the obvious and the sure one)?

Each formula is different in A1 style: A2+B2, A3+B3…etc.

Each formula is completely different in A1 style: A2+B2, A3+B3…etc.

Whereas using R1C1, they are all identical. So this potentially means that wherever you write a formula in that column it will be same, no need to think about which row or column you are in. This is particularly helpful when you are writing VBA code.

How does R1C1 work?

Whenever you’ll use R1C1, you’ll observe that they’re all identical. Therefore, this strongly indicates that whenever you decide to write a formula in the same column, it’ll be same, you do not need to use your energy and confidence in thinking about which row or column you’re in. This is often significant and useful while you plan to write a VBA Code.

So, how are you going to implement R1C1?

Any number in the sq. brackets discusses and indicates about relative distance from the present cell. And this is not like A1 that refers to columns that are followed by the row range, R1C1 will be opposite: rows followed by columns (this isn’t as simple and will take some time in getting used to it).
Positive numbers can discuss with cells below and/or across to the correct.

Negative numbers are going to refer to the cells above and/or to the left.

For example R[2]C[3] could be a cell which is a pair of rows down and three columns to the right.  R[-1]C[-4] and this could be a cell which is one row up and four columns to the left. If no range of numbers are shown in brackets then you’re relating or referring this to the identical row or column i.e. R[3]C is going to be a cell which is three rows below the present/current cell within the SAME column.

Active current cell for Reference style

So, once you have obtained the basics, these are actually not that worse. Make a Note:  you’re not eligible to use an A1 formula when’re about to display R1C1 style and vice-versa. Whatever system you plan to use, Excel ‘translates’ the styles. And this would let you switch between them at any given point.

The other variation present in between the two styles is about the absolute referencing. While you’re in the A1 style, you’ll need to add $ symbols all over the place. If granted you can use F4 which will help you to put them in place but still this is needed to be done. Whereas, in R1C1, you do not need $. If I write R3C4, this means that I’m referring to $D$3. So, if you find Brackets there is no Absolute Reference but if you find that there are no brackets, it’s an absolute reference. This helps out in making the partial absolute references easier to enter to.

The R1C1 style has got major benefits which are pretty much well defined over A1.  But what happens is, we are really used to the A1 style that we cannot decide to move away from this. It would be like an alien concept. But it is advisable that if you ever have a chance, you must try playing with R1C1,

This is going to be really beneficial if you progress in doing VBA, and I guarantee you that writing formulas will be effortless for you.  Try this one out. It is really worth the effort.

Related Items: