6. R1C1-Style Formulas

Referring to Cells: A1 Versus R1C1 References

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 nonissue. 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. After you understand R1C1-style formulas, they are actually more efficient, especially when you are dealing with writing formulas in VBA. Using R1C1-style addressing allows you to write more efficient code. Plus, there are some features such as setting up array formulas where you are required to enter the formula in R1C1 style.

I can hear the collective groan from Excel users everywhere. You could skip these pages of 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.

Switching Excel to Display R1C1-Style References

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

Figure 6.1. Selecting the R1C1 reference style on the Formulas category of the Excel Options dialog causes Excel to revert to 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 6.2).

Figure 6.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 of weeks, someone manages to accidentally turn this option on, and we get an urgent support request at MrExcel. This style is foreign to 99 percent of spreadsheet users.

The Miracle of Excel Formulas

Automatically recalculating thousands of cells is the main benefit of electronic spreadsheets over the green ledger paper used up until 1979. However, a close second-prize award would be that you can enter one formula and copy that formula to thousands of cells.

Enter a Formula Once and Copy 1,000 Times

Consider this simple worksheet in Figure 6.3. Enter a simple formula such as =C4*B4 in cell D4, double-click the AutoFill handle, and the formula intelligently changes as at is copied down the range.

Figure 6.3. Double-click the AutoFill handle, and Excel intelligently copies this relative-reference formula 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.

The numeric results in Figure 6.4 are achieved by the formulas shown in Figure 6.5.

Figure 6.4. These results in Columns D, F, and G are achieved by the formulas shown in Figure 6.5.

Figure 6.5. Press Ctrl+` to switch to showing formulas rather than their results. It is amazing that Excel adjusts the cell references in each formula as you copy down the column.

Considering that you had to enter formulas only in Rows 4 and 10, it is amazing that Excel was able to intelligently copy the formulas down the column.

Excel users take this behavior for granted, but people in beginning Excel classes are amazed that the formula =F4+D4 in cell G4 automatically changed to =F5+D5 when it was copied to cell G5.

The Secret: It’s Not That Amazing

Remember that Excel does everything in R1C1-style formulas. Excel shows addresses and formulas in A1 style merely because it needs to adhere to the standard made popular by VisiCalc and Lotus.

If you switch the worksheet in Figure 6.5 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 6.6, you will see that in R1C1 language, every formula in Column D 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 6.6. The same formulas in R1C1 style. Note that every formula in Column 4 or Column 6 is the same as all other formulas in that column.

This is one of the reasons why R1C1-style formulas are more efficient in VBA. You can enter the same formula in an entire range of data in a single statement.

Explanation of R1C1 Reference Style

An R1C1-style reference includes the letter R to refer to row and the letter C to refer to column. Because the most common reference in a formula is a relative reference, let’s look at relative references in R1C1 style first.

Using R1C1 with Relative References

Imagine you are entering a formula in a cell. To point to a cell in a formula, you use the letters R and C. After each letter, enter the number of rows or columns in square brackets.

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. 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. 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 square brackets 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

• 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 never do this because it would create a circular reference.

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

Figure 6.7. 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, the formula is this:

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

Using R1C1 with Absolute References

An absolute reference is one where the row and column remain fixed when the formula is copied to a new location. In A1-style notation, Excel uses a $ before the row number or column letter to keep that row or column absolute as the formula is copied.

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

Using R1C1 with Mixed References

A mixed reference is one where the row is fixed and the column is allowed to be relative, or where the column is fixed and the row is allowed to be relative. This will be useful in many situations.

Imagine 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:

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 R1C1 formulas in this case? A single R1C1 formula with a mixed reference can be used to easily enter a formula to handle an indeterminate number of rows of data (see Figure 6.8).

Figure 6.8. After running the macro, the formulas in Columns E:G of the total row will have a reference to a range that is locked to Row 2, but all other aspects are relative.

Referring to Entire Columns or Rows with R1C1 Style

You will occasionally write a formula that refers to an entire column. For example, you might want to know the maximum value in Column G. If you don’t know how many rows you will have in G, you can write =MAX($G:$G) in A1 style or =MAX(C7) in R1C1 style. To find the minimum value in Row 1, use =MIN($1:$1) in A1 style or =MIN(R1) in R1C1 style. You can use relative reference for either rows or columns. To find the average of the row above the current cell, use =AVERAGE(R[-1]).

Replacing Many A1 Formulas with a Single R1C1 Formula

After you get used to R1C1-style formulas, they actually seem a lot more intuitive to build. One classic example to illustrate R1C1-style formulas is building a multiplication table. It is easy to build a multiplication table in Excel using a single mixed-reference formula.

Building the Table

Enter the numbers 1 through 12 going across B1:M1. Copy and transpose these so the same numbers are going down A2:A13. Now the challenge is to build a single formula that works in all cells of B2:M13 and that shows the multiplication of the number in Row 1 times the number in Column 1. Using A1-style formulas, you must press the F4 key five times to get the dollar signs in the proper locations. The following is a far simpler formula in R1C1 style:

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

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

An Interesting Twist

Try this experiment. Move the cell pointer to F6. Turn on macro recording using the Record Macro button on the Developer tab. Click the Use Relative Reference button on the Developer tab. Enter the formula =A1 and press Ctrl+Enter to stay in F6. Click the Stop Recording button on the floating toolbar.

You get this single-line macro, which enters a formula that points to a cell five rows up and five columns to the left:

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 6.10, meaning that R1C1-style formulas actually wrap from the left side of the workbook to the right side. I cannot think of any instance where this would be actually 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 6.10. The formula to point to five rows above B1 wraps around to the bottom of the worksheet.

Remembering Column Numbers Associated with Column Letters

I like these formulas enough to use them regularly in VBA. I don’t like them enough to change my Excel interface over to R1C1-style numbers. So, I routinely have to know that the cell known as U21 is really R21C21.

Knowing that U is the 21st 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. If you play this little game for a few minutes each day, soon you will memorize the column numbers:

If memorizing column numbers doesn’t sound fun, or even if you have to figure out the column number of Column DGX someday, there is a straightforward way to do so using the Excel interface. Move the cell pointer to cell A1. Hold down the Shift key and start pressing the right-arrow key. For the first screen of columns, the column number appears in the name box to the left of the formula bar (see Figure 6.11).

Figure 6.11. While you select cells with the keyboard, the Name box displays how many columns are selected for the first screen full of columns.

As you keep pressing the right-arrow key beyond the first screen, a ToolTip box to the right of the current cell tells you how many columns are selected. When you get to Column CS, it informs you that you are at Column 97 (see Figure 6.12).

Figure 6.12. After the first screen of columns, a tool tip bar keeps track of the column number.

You could also enter =COLUMN() in a cell to find the column number.

Array Formulas Require R1C1 Formulas

Array formulas are powerful “super-formulas.” At MrExcel.com, we call these CSE formulas because you have to use Ctrl+Shift+Enter to enter them. If you are not familiar with array formulas, they look like they should not work.

The array formula in E20, shown in Figure 6.13, is a formula that does 18 multiplications and then sums the result. It looks like 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 multiplies row by row and then sums the result.

Figure 6.13. The array formula in E20 does 18 multiplications and then sums them. You must use Ctrl+Shift+Enter to enter this formula.

The code to enter these formulas follows. Although the formulas appear in the user interface in A1-style notation, you must use R1C1-style notation for entering array formulas:

Next Steps

Read Chapter 7, “What’s New in Excel 2010 and What’s Changed,” to learn about more features that have changed significantly in Excel 2010.