In this post I mentioned that I prefer the R1C1 style most of the time when I am using Excel. One of the reasons is formula visual consistency.
First a bit of notation. From now on I will refer to a cell as “|ref|” where “ref” is its location in the R1C1 style. For example, the cell to the right of the home cell will be |R2C1|. A reference by itself will not be decorated. So you want to read |R2C1| as “the cell at R2C1″.
In Excel’s notation, a reference is absolute if the row and column are specified as fixed number. For example, R17C3 always refer to the cell at row 17 and column 3. Always.
But in many copy and paste operations, we usually want relative references. Suppose we start with an empty sheet and put the number 42 in |R1C1| and the number 7 in |R1C2|.
Then, in|R2C1|, type
= and then press the up arrow. Excel will show
=R[-1]C in the formula bar. This means “= the cell in the same column and one row above”. The number in the square bracket is the offset, and an offset of zero can be omitted.
Now copy |R2C1| to |R2C2| by copy and paste. Notice that the formula in |R2C2| is still
To toggle the formula view, press
Ctrl-`. It’s a good shortcut to remember.
This is what you should see, roughly:
42 7 =R[-1]C =R[-1]C
Now, in the A1 style:
42 7 =A1 =B1
It’s hard to settle on which one is more visually pleasant, but I think very few people will argue the A1 style is more visually consistent, even in a small example like ours.
P.S. While we are at the topic of relative vs. absolute references, another shortcut to remember is the function key
F4. It cycles among the four possible combinations of relative and absolute references. Go to |R2C1|, press
F2 to modify the content, and press
F4 four times to cycle.
It’s a very handy shortcut if you use it well. For example, I confess that I have trouble counting rows and columns. So if I have to refer to a cell far away, I usually enter its absolute reference, say R42C18, and then press
F4 three times to convert it to the relative reference, which will be RC if the current cell is |R2C1|.