R1C1 Referencing Style

Hello people!! In one of our earlier post, we saw that there is a reference style in Excel named R1C1 style. In that post, I mentioned that we will see about it in one of the future posts. Well, this post is the one. Read on to know more about it.

What is R1C1 Reference Style?

In simple words, R1C1 refers to row 1 column 1. Remember how we refer first cell in a sheet? A1, right? This is what is called A1 reference style. Each cell is named by the column name with row number next to it. Likewise, a cell at the intersection of column C and 2^nd row is named as C2.

In R1C1 style, we refer each row as R and column as C. Row number and column number is mentioned to refer a particular cell. So A1 is referred as R1C1 and C2 is referred as R2C3, so on and so forth.

How to enable R1C1 Style?

Excel by default uses A1 reference style. To turn R1C1 reference style on or off, you will have to do the following.

In Excel 2010 or later

Click File tab > Options. Click on Formulas tab. On the right side, under Working with formulas section, select or clear the R1C1 reference style check box.

In Excel 2007

Click Office button  > Excel Options. Click on Formulas tab. On the right side, under Working with formulas section, select or clear the R1C1 reference style check box.

In Excel 2003 or earlier

Click Options on the Tools menu, and then click the General tab. Under Settings, select or clear the R1C1 reference style check box.

How is it Different from A1 Style?

When you enable this style, column names as you see normally (like A, B, C …..) will be changed to numbers (1, 2, 3 ……..). Also the name box will start showing the reference of the active cell in R1C1 style rather than A1 style. (See top left in the pictures below)

A1 Style

R1C1 Style

Above difference is obvious and trivial. But the real difference will be noticed when you use this style in formulas. We will now compare A1 style and R1C1 style in an example to know what I mean. Let’s do a simple calculation in both the styles. I am adding values in column A and column B in Column C in below screenshots.

A1 Style

R1C1 Style

R1C1 style formulas are looking weird, isn’t it? Don’t fret. I too felt the same at the start. Let’s understand what it means and you will be clear. Before seeing what it is, let’s see the differences in both the screenshots.

Our familiar A1 style has different formulas in each cell of column C. But R1C1 style has the same formula. That’s the cool thing about R1C1 style, wherever you write this formula in column C to add column A and B values, the formula will be same. Let’s breaks down the formula in R1C1 style.

We already know R refers to row and C refers to column. Numbers in brackets are the relative distance between the cell where formula is entered to the cell we are referring. 

In RC[-2]+RC[-1], R without any number next to it means we are referring the same row. C[-2] means 2 columns to the left of the cell. Since our formula is in third column, two columns to the left is the first column (column A in A1 style), in the same row means the second row (where we have first formula), combining both the statements we get cell A2 in our understanding. Similarly RC[-1] means one column to the left in the same row, which is A3.

You might already make a wild guess here. Minus symbol (-) next to C means column to the left so plus symbol (+) means to the right? Yes. Absolutely! Similarly if minus and plus are next to R instead of C, you have to move rows instead of columns. But basic maths logic, if a number is positive, you need not enter plus in front of it. So there is no need to input + before R or C.

Let’s summarise the basics here.

To get familiar with this, let’s see couple of more examples. Assuming you are entering the formula in third row, second column (R3C2 or B3 in A1 style), below are couple of examples.

Ok, if you noticed, all that we discussed above is relative references. What about absolute references?

Your question is timely. Answer to this question also brings up another important advantage of this style. If you want absolute references in A1 style, you will have to enter a cell reference and press F4 or place $ symbols manually, correct? In R1C1, there is no need of $ symbols. R1C1 always refers to first row and first column intersection i.e. equal to $A$1.

In one line, no brackets mean absolute reference. On the same lines, mixed references are also easier to enter.

Why Should We Use R1C1 Style?

We all by default learnt using A1 style in Excel and quite comfortable with it. So, why in the world we should know R1C1 style?

Well, apart from quoting ‘knowledge is wealth’, we have few advantages using R1C1 reference over A1 style. Getting used to this style will be bit confusing at the start but you will love it as you learn it more.

Apart from ease of referring absolute references & uniform formulas when referring relative references, much of the use of R1C1 style is when you write in macro (VBA) code. When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.

History of R1C1 style

We are so used to A1 style so we might think why and how R1C1 style came into existence. Actually, Microsoft started with using R1C1 style by default in its very first spreadsheet program Multiplan. (Read more about this version here). Over the years to take over its main rival Lotus 1-2-3, Microsoft had to adapt Lotus’s referencing style which is A1 style. Eventually this style became default and R1C1 went behind curtains.

That’s all I have to say about R1C1 style. Though A1 style will be what we use mostly and life feels happy with it, try to meddle with R1C1 style once. Just a thought, you may like it.

In one of the future post, we will also see a different style of referencing. More than a style of referencing, that one works much in formulas where cell references spanning across sheets. Stay tuned and Happy Excelling!!