The beauty of the R1C1 Reference Style

If you’ve ever recorded a macro in which you manipulate worksheet formulas, you’ve been confronted with the R1C1 reference style, for example as:

Selection.FormulaR1C1=”=SUM(R[-4]C:R[-1]C)”

What’s with the R1C1 thingy? I typed the formula in the worksheet as =SUM(A1:A4), why did Excel change it?

R1C1 is a reference style for excel cells, just like the more ubiquitous A1 reference style. A1 works with a letter code for the column (A) and a number code for the row(1), with absolute positions preceded by the $ sign.  The R1C1 reference works with numbers for both rows and columns, with the prefix R for Rows,  and C for Columns, with the lack of numbers meaning you work in the same row and / or column as the formula. The numbers that follow R and C are either relative positions (between [ ]) or absolute positions (no [ ]).

Examples:

  • R[2]C refers to the cell two rows below the cell in which the formula’s in: R[2] means two rows below, C without any number means same column
  • RC[-1] refers to the cell one column to the left: R without a number means same row as the formula, C[-1] without any number means same column
  • R1C1 refers the cell in the first row and first cell ($A$1): R1 is first row, C1 is first column.

That’s all well and good, but what’s the point, and why would I need to use it?

1. For Macros:

Imagine you want a whole row of totals, summing the four rows above the total row. If you use A1 addressing, your code will be: Range(“A5:E5″).Formula=”=SUM(A1:A4)”. When you run that code, Excel will actually change the formula for you so it stays relative, i.e. in cell E5, the formula in your worksheet will be =SUM(E1:E4). If you were to just apply the formula to cell E5 though, with the code Range(“E5″).Formula=”=SUM(A1:A4)”, then your worksheet formula would be =SUM(A1:A4). Maintaining that type of code is a guaranteed nightmare.

In R1C1 though, both Range(“A5:E5″).FormulaR1C1=”=SUM(R[-4]C:R[-1]C)”  and Range(“E5″).FormulaR1C1=”=SUM(R[-4]C:R[-1]C)” will put the same formula in cell E5, i.e. =SUM(E1:E4), making it a more consistent choice for coding.

2. For Worksheet Formula Maintenance

You’ve just finished writing a formula, copied it to the whole spreadsheet, formatted everything and you realize that you forgot to make a reference absolute: every formula needed to reference Cell B2 but now, they all reference different cells.

Spreadsheet example where only one cell has the correct information

If your formulas are nicely grouped, you can just copy paste the new formulas, but sometimes, those formulas are scattered throughout the worksheet, so how are you going to do a Find/Replace on the cells, considering that one has B5, the other C12, the third D25, etc., etc.? You can replace B5 by $B$2, then C12 by $B$2 but you will be there all night.

The easy way is to update your Reference Style to R1C1.

What does it matter? Well, When you wrote your first formula back in the beginning of this post, B2 was the cell 4 rows above the cell you wrote it in, i.e. R[-4]C. When you copy it across and down, while the A1 reference changes, the R1C1 reference doesn’t. Throughout the whole spreadsheet, it’s R[-4]C.  If you switch to R1C1 Reference Style (see How you can switch section at the end of this post), you can replace R[-4]C by R2C2 ($B$2) with a simple Find / Replace and be done in one fell swoop.

Another example, if you have a spreadsheet with monthly columns, and want to update the formulas that refer to one month’s column to another, you will have to replace B1 by C1, B20 by C20, etc. You could try doing a find/replace to replace B by C, but in the process, you could turn your SUBTOTAL in SUCTOTAL and create a giant mess. If you switch to R1C1 Reference Style, you can replace RC[-4] by RC[-3] with a simple Find / Replace again. Your mileage may vary depending on your particular set of formulas.

3. Review formula consistency

The most accidental shortcut? Probably Ctrl ~, which switches your worksheet from displaying the results of the formulas to displaying the formula itself. By using that shortcut in the R1C1 style, you have a very visual way of checking the consistency of your formulas across a worksheet. Compare the left column, which has the formulas in A1 style to the formulas in the R1C1 style. As the Excel versions progress, there are more and more warnings on formula inconsistency already, but this is one more handy one.

Comparing Formula display in A1 vs. R1C1

 

How can you switch the Reference Style?

Three Options in Excel 2010:

  1. Go to File \ Options \ Formula \ Working with Formulas, Check R1C1 reference style (this is the long way)
  2. Open Visual Basic Editor (Alt + F11), open an Immediate window (Ctrl+G), type application.ReferenceStyle=xlR1C1, Enter. This is the quick, easy fix. To reverse, type application.ReferenceStyle=xla1 and Enter
  3. Put the following macro in your workbook, and add a button for it in your Quick Access toolbar, it’s well worth it.

Sub Shift_R1C1_A1()
If application.ReferenceStyle = xlR1C1 Then
application.ReferenceStyle = xlA1
Else
application.ReferenceStyle = xlR1C1
End If
End Sub

Try it, you might like it.

Thomas

PS: for more on the history of the R1C1 evolution, and after you’ve pressed the Follow button, check out this blog post by excelmate (will open in new tab).

About these ads
This entry was posted in Macros.

4 comments on “The beauty of the R1C1 Reference Style

  1. Lori says:

    When you said “you can replace R[-4]C by R2C2 ($B$2) with a simple Find / Replace and be done in one fell swoop” Did you mean, replace R[4]C with R-4C, since it is 4 rows from the current cell?

    So therefore the new formula would would reference to R-4C, correct?

    • In this case, I want to turn a relative reference to R[-4]C to an absolute reference to R2C2, so the new reference will be to R2C2. In addition, R-4C is not a R1C1 reference without the [].

  2. I love R1C1. I started in 1982 as you referenced with Multiplan on a DEC Rainbow with only 2 disc drives. I’ve used only the R1C1 style until in the last week or so (I don’t know if it’s from saving a template somebody did with A1 style or what) it’s locked on A1 style. I’ve gone to Options/Formulas and find NO boxes checked when there had been the R1C1 checked before. So I check the box, Save it and leave Options only to find NO change. What can I do? the A1 is driving me nuts.

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