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.
4 responses to “Combining R1C1 and A1-style cell referencing in Excel”
This is really useful! I have columns of data, always starting in row 11 but the number of rows varies from one worksheet to another. I can now get mean, standard deviation and so on for any column by means of a single formula which I can just copy across from worksheet to worksheet, without having to manually enter the range of rows required. For example, if I leave two blank lines below the data and enter
=COUNTIF(INDIRECT("R11C:R[-3]C",FALSE),">0")I get a count of the number of non-zero entries, and
=AVERAGEIF(INDIRECT("R11C:R[-4]C",FALSE),">0")on the next row gives the average of the non-zero values.
Brilliant! Thank you very much. I had to set-up conditional formatting of cells in one column based on values in another column but not on the same row (the next one). I used the following formula to format the cells conditionally: =INDIRECT(“RC[-1]”,FALSE)>INDIRECT(“RC”,FALSE)
I have to include the “R” in the indirect formule in order to make it work:
The indirect() function cannot be optimised by Excel and so your spreadsheet will recalculate everytime a cell is changed; which can really slow things down. Rather than embedding the R1C1 formula inside indirect(), just toggle the spreadsheet into R1C1 addressing mode (open the Options panel) and type the formulae needed. You’ll be able to visually inspect them and verify they’re consistent. Then put the spreadsheet back in A1 mode.
I’ve coded an addin that puts an address toggle button in the ribbon: https://rath.ca/Misc/VBA/Excel/RC_A1_Toggle_v3.0.zip
Alternatively, put this macro in your Personal macros list and use it:
' RC_A1_Toggle Macro
' Toggle between RC and A1 addressing modes.
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Application.ReferenceStyle = xlR1C1