LyteByte

There are two types of reference styles in Excel. The first one is A1 reference style where the columns are labeled using the Alphabets and the rows are labeled using numbers. In the R1C1 reference style both the rows and columns are labeled using numbers. Earlier versions of Excel from MS Office came with only R1C1 reference style. But recent versions like Office 2007 has both referencing styles.

For example, in A1 Reference Style, E15 refers to the cell in 15^th row and E^th (5^th) column and the same cell in R1C1 Reference Style uses R15C5 (R followed by the row number and C followed by the column number).

I have always preferred to use A1 reference style as both the labels for rows and columns are different. But with the R1C1 reference style since both rows and columns are numbered its easy to get confused (especially while writing formulas)

Also these reference styles are not only different in addressing but also very different in the way they are referenced in formulas or when selecting a range of cells.

In A1 reference style, the range is referred by the starting cell address and ending cell address with a colon in between. Example, A1:A5. This refers to the first five cells in the first column and this reference remains the same where ever the range is called within the sheet.

But in R1C1 reference style, the range is referred by how far the cells are located from the cell you are calling. For example, if you have 5 values from R1C1 to R5C1 and the range is called from R7C2, then the range would be R[-6]C[-1]:R[-2]C[-1]. Here the first cell in the range is 6 rows before the cell R7C2 and 1 column before the cell R7C2 and similarly for the last cell in the range. This method of referencing is called Relative Referencing.

Though relative referencing is used by default in R1C1 reference mode, one can also use absolute reference with R1C1. For the same example as above, with absolute reference, the range would be referred as R1C1:R5C1. To know more about referencing, read How to use relative and absolute reference in Excel?

Benefits of A1 and R1C1 Reference Style

For human computation and understanding A1 reference style is better but if you are writing a macro, then R1C1 reference style would be better to compute with referencing to the positions. If you are writing a macro but you don’t want to get confused then you may switch to A1 reference style and write all the formulas and then switch back to R1C1 reference style to get the value of the formulas in this reference style. (Read more on: How to switch between A1 and R1C1 Reference Style in Excel?)