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.

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.

How can you switch the Reference Style?

Three Options in Excel 2010:

Go to File \ Options \ Formula \ Working with Formulas, Check R1C1 reference style (this is the long way)

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.

Maybe you accidentally changed the setting in your template? In that case, try this: go into excel, [alt-f11], [ctrl-g], now type in ? application.StartupPath
NOTE: there is a space after the ? and a period after application. Now hit enter. Copy that path name into your file explorer.
You are now in the “xlstart” directory, if there’s a personal workbook there then open it and change the settings to the R1C1 reference style and save.

If this doesn’t work then open a new workbook and change the settings to the R1C1 reference style and save as an “Excel template” (file extension .xlt) in the xlstart directory mentioned above. This should fix the problem, hope this helps!