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.

ScreenHunter_67 Apr. 22 13.42

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:

ScreenHunter_67 Apr. 22 13.43

And now for R1C1….

ScreenHunter_58 Mar. 18 15.05

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.

R1C1 ref

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/)

Advertisements

Posted on April 22, 2013, in General, VBA and tagged , , , . Bookmark the permalink. 17 Comments.

  1. I use R1C1 mode almost exclusively, and Excel behaves badly when I send people my workbooks: it switches other user’s Excel into R1C1 mode when they open my workbooks — and confuses the heck out of them. So, I’ve written an add-in that solves the problem: http://rath.ca/Misc/VBA/Excel/RC_A1_Toggle_v2.zip

  2. The add-in automatically switches Excel to A1 mode before saving the file, then switches back to R1C1 after the save is complete. This ensure that I always work in R1C1 mode but no one else is negatively affected. The add-in also provides a button to switch between modes.

  3. Brad Willingham

    I feel like the last of a species that has just found others of my kind. I’ve used the R1C1 style exclusively since 1990 and have never run into anyone else who uses it. Thanks for the blog post.

  4. awsome explanation

  5. The thing is, even with the occasional $ sign, A1 style addresses are still more compact and this matters for the readability of large formulae.
    The advantage that formulae in adjoining cells all look the same vanishes once you realise that when Excel copies formulae it will copy the relative distances (when there is no $).
    In the end the only thing you gain with R1C1 notation is a useless R, a useless C, and a metric tonne of brackets.

  6. Thank you so much. This is awesome.

  7. Great Example.. Thanks a lot..

  8. Thanks for the link and comments. I’ll do the same from my end too.

  1. Pingback: The beauty of the R1C1 Reference Style « A CFO's Excel blog

  2. Pingback: Combining R1C1 and A1-style cell referencing in Excel | Adam Dimech's Coding Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: