﻿ Combining R1C1 and A1-style cell referencing in Excel | Adam Dimech's Coding Blog

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's 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. . 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.

3 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:

=SUM(INDIRECT(“RC[-1]”;FALSE)+$A$2)
^

<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>