Combining R1C1 and A1-style cell referencing in Excel
Use R1C1 cell referencing in Excel equations whilst the programme is still set to A1-style.
Excel users may be surprised to learn that there are actually two referencing systems in Excel: the “A1” system and the “R1C1” system.
Without any doubt, the “A1” system (where rows are numbered and columns are lettered from A to XFD) is the most widely used, but those who’ve coded in Visual Basic would be familiar with the “R1C1” system.
It’s probably fairly obvious that the “R” in R1C1 stands for “row” and the “C” for column. Each is then numbered accordingly. Cell A6 translates as R6C1. Cell B4 translates as R4C2 et cetera.
Whilst R1C1 may appear to be unnecessarily complicated, its true value comes from its system of denoting relative references. Square brackets are incorporated into the formula to indicate a relative reference in a manner not possible with A1 referencing.
Whilst Excel will permit a user to switch between A1 and R1C1 referencing overall, that’s not always desirable. Thankfully there’s a way to include R1C1 references into an A1-style sheet using the INDIRECT function.
INDIRECT function works by converting text into a cell reference. The INDIRECT function only has two parameters; Reference Text (
Ref_text) and an
a1 True/False statement to indicate whether the formula is A1-Style (True) or R1C1 style (False).
Simply include an
INDIRECT function within your equation where
a1=FALSE and the R1C1 reference can be seamlessly included in a sheet with A1-style cell referencing.
In the following table, there are two columns:
The “Total” column shows the value in the “Number” column added to the value in cell A2 (9). Using A1-style referencing, I could type the following into cell B2:
The problem is that when I go down to cell B3, the formula is different:
To ensure that the same formula is applied all the way down, I used an R1C1 reference within an INDIRECT function:
Whilst this is a very simple example of its use, the applications (and benefits) are considerable where more complicated formulae are required.