How to Update the Feed in Excel to Use A1 or R1c1 Style References

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:

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

lawlorantrume.blogspot.com

Source: https://tduhameau.wordpress.com/2012/09/27/the-beauty-of-the-r1c1-reference-style/

Share this post