Excel – R1C1 Reference Style vs. A1

If you have ever strayed into the Options screens of Excel you may have noticed something called R1C1 reference style. You may have even tried it and when you saw that all the columns had changed from letters to numbers, panicked and switched it back.

However, the R1C1 style has some advantages over the standard A1 style, but it does take a bit of getting used to!

So is this R1C1 style new? Well in fact not at all. This cell reference style dates back to 1982 and the introduction of Multiplan as a rival to VisiCalc and Lotus 1-2-3. Multiplan was developed by Microsoft for Apple Macintosh and used this cell referencing style instead of what was to become the standard in spreadsheets – A1 style.

Over the next few years, Lotus 1-2-3 became “the” spreadsheet package. In order for Microsoft to steal Lotus users who were used to the A1 style they added the A1 style to Excel so that people who migrated across would find it familiar. Excel eventually overtook Lotus in 1993 as the industry standard for spreadsheets. The rest as they say 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.

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:

And now for R1C1….

At first this looks quite horrific and ten times more complicated that the A1 style. Before I break down how it works, what do you notice between the two styles (other than the obvious one)?

Each formula is 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.

So how does it work?

Any numbers in square brackets refer to relative distance from the current cell. Unlike A1 which refers to columns followed by row number, R1C1 does the opposite: rows followed by columns (which does take some getting used to).

Positive numbers will refer to cells below and/or across to the right.

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

For example R[2]C[3] is a cell 2 rows down and 3 columns to the right. R[-1]C[-4] is a cell 1 row up and 4 columns to the left. If no number is shown in brackets then you are referring to the same row or column i.e. R[3]C will be a cell 3 rows below the current cell in the SAME column.

So once you have the basics, they are actually not that bad. Note however, that you cannot use an A1 formula when displaying R1C1 style and vice-versa. Whichever system you use, Excel ‘translates’ the styles should you switch between them at any point.

The other difference between the two styles is absolute referencing. In the A1 style I need to add $ symbols all over the place. Granted I can use F4 to put them in place for me but it still needs to be done. In R1C1, there are no $. If I write R3C4 I am referring to $D$3. So if there are no brackets, it’s an absolute reference. This makes partial absolute references easier to enter too.

The R1C1 style definitely does have some neat advantages over A1, but we are so used to the A1 style that moving away from it is an alien concept. But if you ever have a chance, try playing with R1C1, especially if you progress to VBA, and I can guarantee that writing formulas will become a lot easier…the pain is only short-lived but it is worth the effort.

For a bit of further reading on R1C1 follow this link (http://tduhameau.wordpress.com/2012/09/27/the-beauty-of-the-r1c1-reference-style/)