Skip to content

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.

Image via Excelmate

. An illustrative example of how relative R1C1 cell referencing works. Image via Excelmate

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.

The 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.

An example

In the following table, there are two columns:

Number Total
9 18
4 13
4 13
9 18
8 17
5 14

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:

=SUM(A2+$A$2)

The problem is that when I go down to cell B3, the formula is different:

=SUM(A3+$A$2)

To ensure that the same formula is applied all the way down, I used an R1C1 reference within an INDIRECT function:

=SUM(INDIRECT("C[-1]",FALSE)+$A$2)

Whilst this is a very simple example of its use, the applications (and benefits) are considerable where more complicated formulae are required.

   

Comments:

2 responses to “Combining R1C1 and A1-style cell referencing in Excel”

  • Written by Russell Gerrard on 22 June 2016:

    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.

  • Written by Dieter Spoerri on 26 February 2017:

    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(“R[1]C[-1]”,FALSE)>INDIRECT(“RC”,FALSE)

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.