aka Turning the R1C1 Address Option On and Off
A client called me a while back with a terrifying problem: all of the column letters in Excel had become numbers and she had no idea how it got that way or how to write formulas anymore. Rest assured, it was not the beginning of the robot rebellion, and we were able to get her back up and running right away.
The problem was that R1C1 notation had gotten accidentally turned on in her Options panel. Before I tell you how to turn it on and off, I would like to briefly mention what R1C1 notation is good for, and how it works in case you run across it in a co-worker’s workbook.
R stands for Row, and C stands for Column
A R1C1 cell notation is reversed from a normal Excel cell reference — you list the row number, and then the column number. The cell E2 would be referenced R2C5 in a R1C1 workbook. A normal Excel formula might look something like =E2*E3, but its R1C1 counterpart would be =R2C5*R3C5. If that confuses you, don’t worry about it. You can always jump ahead and learn how to turn it off!
People who write a lot of VBA code often prefer R1C1 notation because it makes it easy for them to extract the row and column number. There are other, more complicated reasons why you might want to use R1C1, especially if you are worried about Relative and Absolute references in complex formulas.
When you write a cell reference as R2C5 it is automatically an Absolute reference, and is the same thing as writing $E$2. If you want to use a Relative reference in R1C1 format (“normal” Excel E2), you would write it as R[2]C[5]. The square brackets tell Excel to make the reference relative to the cell’s location.
If you are interested in exploring this topic further, check out R1C1, the unused Excel cell reference system on Numbergrindr as a starting point.
Turning it On and Off
Fortunately, no suave music is needed to turn R1C1 on and off. You just need to use the Options window.
- Click the File tab.
- Click the Options button on the left side.
- Click the Formulas button in the Excel Options window.
- Check or un-check as appropriate the R1C1 reference style checkbox in the Working with formulas section.
