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 2nd 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 |
 |
| A1 Style |
 |
| R1C1 Style |
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.
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!!
