5. R1C1-Style Formulas

We can trace the A1 style of referencing back to VisiCalc. Dan Bricklin and Bob Frankston used A1 to refer to the cell in the upper-left corner of the spreadsheet. Mitch Kapor used this same addressing scheme in Lotus 1-2-3. Upstart Multiplan from Microsoft attempted to buck the trend and used something called R1C1-style addressing. In R1C1 addressing, the cell known as A1 is referred to as R1C1 because it is in row 1, column 1.

With the dominance of Lotus 1-2-3 in the 1980s and early 1990s, the A1 style became the standard. Microsoft realized it was fighting a losing battle and eventually offered either R1C1-style addressing or A1-style addressing in Excel. When you open Excel today, the A1 style is used by default. Officially, however, Microsoft supports both styles of addressing.

You would think that this chapter would be a non-issue. Anyone who uses the Excel interface would agree that the R1C1 style is dead. However, we have what on the face of it seems to be an annoying problem: The macro recorder records formulas in the R1C1 style. So you might be thinking that you just need to learn R1C1 addressing so that you can read the recorded code and switch it back to the familiar A1 style.

I have to give Microsoft credit. R1C1-style formulas, you’ll grow to understand, are actually more efficient, especially when you are dealing with writing formulas in VBA. Using R1C1-style addressing enables you to write more efficient code. Plus, there are some features such as setting up array formulas that require you to enter a formula in R1C1 style.

I can hear the collective groan from Excel users everywhere. You could skip these pages on this old-fashioned addressing style if it were only an annoyance or an efficiency issue. However, because it is necessary to understand R1C1 addressing to effectively use important features such as array formulas, you have to dive in and learn this style.

To switch to R1C1-style addressing, select Options from the File menu. In the Formulas category, select the R1C1 Reference Style check box (see Figure 5.1).

Figure 5.1 Selecting the R1C1 reference style in the Formulas category of the Excel Options dialog causes Excel to use R1C1 style in the Excel user interface.

After you switch to R1C1 style, the column letters A, B, C across the top of the worksheet are replaced by numbers 1, 2, 3 (see Figure 5.2).

Figure 5.2 In R1C1 style, the column letters are replaced by numbers.

In this format, the cell that you know as B5 is called R5C2 because it is in row 5, column 2.

Every couple weeks, someone manages to accidentally turn on this option, and we get an urgent support request at MrExcel. This style is foreign to 99% of spreadsheet users.

Figure 5.3 Double-click the AutoFill handle, and Excel intelligently copies this relative-reference formula down the column.

The formula is rewritten for each row, eventually becoming =C9*B9. It seems intimidating to consider having a macro enter all these different formulas. Figure 5.4 shows how the formulas change when you copy them down columns D, F, and G.

Figure 5.4 Amazingly, Excel adjusts the cell references in each formula as you copy down the column.

The formula in cell F4 includes both relative and absolute formulas: =IF(E4,ROUND(D4*$B$1,2),0). Thanks to the dollar signs inserted in cell B1, you can copy down this formula, and it always multiplies the total price in this row by the tax rate in cell B1.

If you switch the worksheet in Figure 5.4 to use R1C1 notation, you will notice that the “different” formulas in D4:D9 are all actually identical formulas in R1C1 notation. The same is true of F4:F9 and G4:G9.

Use the Options dialog to change the sample worksheet to R1C1-style addresses. If you examine the formulas in Figure 5.5, you will see that in R1C1 language, every formula in column 4 is identical. Given that Excel is storing the formulas in R1C1 style, copying them, and then merely translating to A1 style for us to understand, it is no longer that amazing that Excel can easily manipulate A1-style formulas as it does.

Figure 5.5 The same formulas in R1C1 style. Note that every formula in column 4 is the same, and every formula in column 6 is the same.

This is one of the reasons R1C1-style formulas are more efficient than A1-style formulas in VBA. When you have the same formula being entered in an entire range, it is less confusing.

The following list explains the “rules” for using R1C1 relative references:

For columns, a positive number means to move to the right a certain number of columns, and a negative number means to move to the left a certain number of columns. For example, from cell E5, use RC[1] to refer to F5 and RC[-1] to refer to D5.

For rows, a positive number means to move down the spreadsheet a certain number of rows. A negative number means to move toward the top of the spreadsheet a certain number of rows. For example, from cell E5, use R[1]C to refer to E6 and use cell R[-1]C to refer to E4.

If you leave off the number for either the R or the C, it means that you are pointing to a cell in the same row or column as the cell with the formula. For example, the R in RC[3] means that you are pointing to the current row.

If you enter =R[-1]C[-1] in cell E5, you are referring to a cell one row up and one column to the left. This would be cell D4.

If you enter =RC[1] in cell E5, you are referring to a cell in the same row but one column to the right. This would be cell F5.

If you enter =RC in cell E5, you are referring to a cell in the same row and column, which is cell E5 itself. You would generally not do this because it would create a circular reference.

Figure 5.6 shows how you would enter a reference in cell E5 to point to various cells around E5.

Figure 5.6 Here are various relative references. These would be entered in cell E5 to describe each cell around E5.

You can use R1C1 style to refer to a range of cells. If you want to add up the 12 cells to the left of the current cell, you use this formula:

=SUM(RC[-12]:RC[-1])

To always refer to an absolute row or column number, just leave off the square brackets. This reference refers to cell $B$3, no matter where it is entered:

=R3C2

Imagine that you have written a macro to import Invoice.txt into Excel. Using .End(xlUp), you find where the total row should go. As you are entering totals, you know that you want to sum from the row above the formula up to row 2. The following code would handle that:

Click here to view code image

Sub MixedReference()
    TotalRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(TotalRow, 1).Value = "Total"
    Cells(TotalRow, 5).Resize(1, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
End Sub

In this code, the reference R2C:R[-1]C indicates that the formula should add from row 2 in the same column to the row just above the formula in the current column. Do you see the advantage to using R1C1 formulas in this case? You can use a single R1C1 formula with a mixed reference to easily enter a formula to handle an indeterminate number of rows of data (see Figure 5.7).

Figure 5.7 After the macro has run, the formulas in columns 5:7 of the total row will have a reference to a range that is locked to row 2, but all other aspects are relative.

Click here to view code image

Sub MultiplicationTable()
    ' Build a multiplication table using a single formula
    Range("B1:M1").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
    Range("B1:M1").Font.Bold = True
    Range("B1:M1").Copy
    Range("A2:A13").PasteSpecial Transpose:=True
    Range("B2:M13").FormulaR1C1 = "=RC1*R1C"
    Cells.EntireColumn.AutoFit
End Sub

The R1C1-style reference =RC1*R1C could not be simpler. In English, it is saying, “Take this row’s column 1 and multiply it by row 1 of this column.” It works perfectly to build the multiplication table shown in Figure 5.8.

Figure 5.8 The macro creates a multiplication table. The formula in B2 uses two mixed references: =$A2*B$1.

Click here to view code image

Sub Macro1()
    ActiveCell.FormulaR1C1 = "=R[-5]C[-5]"
End Sub

Now, move the cell pointer to cell A1 and run the macro that you just recorded. You might think that pointing to a cell five rows above A1 would lead to the ubiquitous Run Time Error 1004. But it doesn’t! When you run the macro, the formula in cell A1 is pointing to =XFA1048572, as shown in Figure 5.9, meaning that R1C1-style formulas actually wrap from the left side of the workbook to the right side. I cannot think of any instance in which this would actually be useful, but for those of you who rely on Excel to error out when you ask for something that does not make sense, be aware that your macro will happily provide a result and probably not the one that you expected!

Figure 5.9 The formula to point to five rows above B1 wraps around to the bottom of the worksheet.

Knowing that U is the twenty-first letter of the alphabet is not something that comes naturally. We have 26 letters, so A is 1 and Z is 26. M is the halfway point of the alphabet and is column 13. The rest of the letters are not particularly intuitive. A quick way to get the column number for any column is to enter =COLUMN() in any empty cell in that column. The result tells you that, for example, DGX is column 2910 (see Figure 5.10).

Figure 5.10 Use the temporary formula =COLUMN() to learn the column number of any cell.

You could also select any cell in DGX, switch to VBA, press Ctrl+G for the Immediate window, type ? ActiveCell.Column, and press Enter.

The array formula in F4 in Figure 5.11 is a formula that does more than 19,000 multiplications and then sums the result. It looks as though this would be an illegal formula. In fact, if you happen to enter it without using Ctrl+Shift+Enter, you get the expected #VALUE! error. However, if you enter it with Ctrl+Shift+Enter, the formula miraculously pops out an array of 19,000 values and evaluates each one.

Figure 5.11 The array formula in F4 does 19,000 calculations. You must use Ctrl+Shift+Enter to enter this formula.

Note that although the formulas appear in the user interface in A1-style notation, you must use R1C1-style notation for entering array formulas.