Learn R1C1 Referencing to Understand Formula Copying

November 30, 2021 - by Bill Jelen

Problem: All of a sudden, the column letters along the top of my spreadsheet have been replaced by numbers. None of the formulas I enter will work. What’s wrong?

Strategy: Relax. There are two ways of naming cells, and in this case, someone has turned on the R1C1 style of addressing. To return to the normal A1 style of cell addressing, select File, Options, Formulas. Uncheck the R1C1 Reference Style check box.

But wait. While you are here, you can learn something fascinating about spreadsheets. In the topic “Copy a Formula That Contains Relative References,” I say that it’s miraculous that Excel can automatically change a formula as you copy it. If you take a couple of minutes to learn about this other method of cell addressing, you will understand that it may not be so amazing after all.

When Dan Bricklin and Bob Frankston invented VisiCalc, they used the A1 style of cell naming. When Mitch Kapor started selling Lotus 1-2-3, he used the same style. When Microsoft came out with its first spreadsheet product, Microsoft Multiplan, it used a very different method of cell addressing, known as R1C1. In the Microsoft system, the rows are numbered just as in the A1 system. However, the columns are also numbered. Each cell is given a name, such as “R4C8,” which stands for the cell at row 4, column 8. This is the cell that you and I know as H4.

In the R1C1 style, the formulas are interesting. Look at the A1-style formula shown in cell D6.

The formula in the formula bar says =D5+C6-B6. But think about this formula in plain language. What it really means is “Take the cell just above me, add the interest in the cell just to the left of me, and subtract the payment in the cell two cells to the left of me.” Formulas in R1C1 style are rather like this plain language description. If you want to enter a formula in D6 that points to the cell just above, for example, you use =R[-1]C. The number in square brackets after the R indicates to how many rows ahead or back you are referring. In this case, row 5 is one row above row 6, so you put a -1 in the square brackets. There is no number after the C portion of the address, which means you are referring to the same column as the cell that contains the formula.

To refer to a cell that is two cells to the left of the cell with the formula, you use =RC[-2].

The formula can be restated in R1C1 style as follows: =R[-1]C+RC[-1]-RC[-2].

All relative references in R1C1 style have a number in square brackets—either after the R or after the C or both.

It is very interesting to see how this style handles absolute addresses. Say that you have a formula that points to $E$2. This reference in R1C1 language would be =R2C5. Notice that when there are no square brackets, the formula is saying to always point to row 2 and column 5.

It is also possible to have mixed references. RC1 means this row, but always column A.

Additional Details: Now that you understand the basics of R1C1-style formulas, you can appreciate how Excel can automatically change a formula as you copy it. Remember that Microsoft invented this method for its Multiplan product. Lotus 1-2-3 was the dominant spreadsheet in the late 1980s and early 1990s. Microsoft was battling for market share. Everyone using spreadsheets was familiar with the A1 style. No one would want to learn the R1C1 style in order to switch to Microsoft. So, in its Excel product, Microsoft developed an elaborate system to actually store the formulas in R1C1 style but to translate the R1C1 formulas to A1 style to make it easier for all the Lotus fans to understand.

By default, Microsoft starts with A1-style addressing. However, remember that you are just one check mark away from switching to R1C1-style addressing.

To really see R1C1 in its glory, examine the amortization table example in Formula View mode. (Press Ctrl+` to toggle into Formula View mode.) Below is Formula View mode in A1 style. As you can see, every formula in column D is different.

Below shows the Formula View mode in R1C1 style.

In A1 style, it seems amazing that Excel can change a reference from D10 to D11 when the formula is copied down. However, look closely at the formulas in each row of rows 7 and higher in the R1C1 style shown in this figure. Each formula in a column is identical to the formula located just above it!

While VisiCalc and Lotus 1-2-3 made the formula replication seem amazing because of their A1 reference style, if the Multiplan invention of R1C1 style had taken hold, it would not seem amazing at all because, in fact, every formula is exactly identical as you copy it down through the rows. Microsoft actually had a better system. Just as Beta was superior to VHS but fell by the wayside due to market share, Microsoft’s superior R1C1 style lost its battle, and Microsoft chose A1 style as the default in Excel.

If you ever plan on writing VBA macros in Excel, it is important that you understand the R1C1 style of formulas. For general use in Excel, you never really need to totally understand the R1C1 style, but it is interesting to see how Microsoft’s R1C1 style is actually better than A1 when you’re copying formulas in a spreadsheet.

This article is an excerpt from Power Excel With MrExcel

Title photo by Alexey Derevtsov on Unsplash