Relax. There are two ways of naming cells. Someone has turned on the R1C1 style of addressing. To return to the normal A1 style of cell addressing, go to Tools – Options. On the General tab, uncheck the box for R1C1 Reference Style, as shown in Fig. 198.
click to enlarge
Alternatively, if you're working in Excel 2007 or later, click the Office button in the upper left corner of the application and select
Excel Options. Then select
Formulas from the left menu and
uncheck the box next to R1C1 reference style.
click to enlarge
But wait – while you are here, you can learn something fascinating about spreadsheets. In the topic Copy a Formula That Contains Relative References, I suggested it was miraculous that Excel could automatically change a formula as you copied it. If you take two 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 their first spreadsheet product – Microsoft Multiplan – they used a very different method of cell addressing. This method is 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”.
This name 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 this formula in cell D6, as shown in Fig. 199.
click to enlarge
The formula in the formula bar says =D5+C6–B6. But when you think about this formula in plain language, what it is 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 more like the plain language description above. If you want to enter a formula in D6 that points to the cell just above, it would be =R[–1]C. The number in square brackets after the R indicates to how many rows ahead or back you are referring. In our case, row 5 is one row above row 6, so you would put a –1 in the square brackets.
There is no number after the C portion of the address, which means that you are referring to the same column as the cell that contains the formula.
If you want to refer to a cell that is two cells to the left of the cell with the formula, you would use =RC[–2].
As shown in Fig. 200, the formula from Fig. 199 can be restated in R1C1 style as follows:
=R[–1]C+RC[–1]–RC[–2]
click to enlarge
So, 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 does absolute addresses. As shown in Fig. 201, the formula in B6 is an absolute formula that always points to cell E2. The formula in A1 style is =$E$2.
click to enlarge
To enter a similar absolute reference in R1C1 style, you do not include square brackets in the address. As shown in Fig. 202, a formula of =R2C5 will ALWAYS point to cell E2.
click to enlarge
It is also possible to have mixed references. Flip back to Fig. 177 in the multiplication table topic. Fig. 203 shows that formula in R1C1 style:
click to enlarge
