Excel tools to demonstrate

Showing Formulas in R1C1 Notation

Speaking of relative and absolute addresses in formulas, it’s pretty amazing how intelligent spreadsheets are. When you copy a formula in cell C1 such as =A1+B1 down, it automatically changes appropriately: =A2+B2, then =A3+B3, etc. In a sense, these are all the same formula. Each says to add the two values to the left of the current cell. Excel allows you to see this equivalence even more clearly by viewing the formulas in a different format, called R1C1 notation. In this format, each of the formulas in column C is written as =RC[-2]+RC[-1]. R stands for row, and C stands for column. The fact that there is nothing next to R means we stay in the same row. The numbers in brackets next to C mean to go 2 columns to the left and 1 column to the left. (For columns, negative numbers mean to go to the left, positive to the right. For rows, negative numbers mean to go up, positive down.)

If there is a number next to R or C that is not in brackets, it indicates an absolute reference. For example, =R2C[2] placed in cell D5 is equivalent to =F$2 since the row reference is absolute and the column reference is relative to column D.

The usual way of expressing formulas, such as =C5+D5, is called A1 format. The new way discussed here is called R1C1 format. You can easily toggle between them.

To toggle between A1 and R1C1 formats:

Select the Office button, select Excel options, select Formulas, and check or uncheck the R1C1 reference style option.

Try it! Toggle between A1 and R1C1 reference style in the following spreadsheet, and for either, examine the formulas inside the border and in the Total row. (Note: As far as I can tell, you’ll have to actually open Excel to try this one. When you double-click on the spreadsheet below, the Excel menu appears, but not the Office button. So you evidently can’t change the Excel options from within Word. I suppose that makes sense.)

I have read one Excel book that advocates the use of R1C1 notation everywhere, reasoning that this notation makes more logical sense. Maybe the author has a point, but the A1 notation is so ingrained in most of us that the transition to R1C1 notation would probably start a revolt among millions of Excel users!