Skip to content

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.

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

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

On 22 June 2016, Russell Gerrard wrote: Hyperlink chain icon

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.

Reply

On 26 February 2017, Dieter Spoerri wrote: Hyperlink chain icon

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)

Reply

On 1 February 2020, Luc Mettepenninge wrote: Hyperlink chain icon

I have to include the “R” in the indirect formule in order to make it work:

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

Reply

On 24 May 2023, Christopher Rath wrote: Hyperlink chain icon

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:

Sub RC_A1_Toggle()
'
' RC_A1_Toggle Macro
' Toggle between RC and A1 addressing modes.
'
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlR1C1
End If
End Sub

Reply

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.