Tag Archives | R1C1 reference

Using the R1C1 Reference Style in Excel

There is an option within Excel to use the R1C1 reference style. This is not normally done as most users are comfortable with using the usual Column Letter and Row Number format for references in formulas. However, this can be changed by going to Excel options, selecting the Formulas category and then ticking the box for the R1C1 Reference Style. When you have done this you will notice back in the Excel workbook that the column letter header row has changed to numbers to reflect the changed referencing style.

The diagram here show how you would enter a reference in cell C3 (the grey cell) to point to different cells around C3. For example to point to to one row above the current cell in the same column would require a reference of =R[-1]C whereas to point to the cell one column to the right in the same row would require =RC[1] as a formula.

Releative

So a positive number in square brackets after R means a number of rows below, a positive number in square brackets after C means a number of columns to the right. A negative number in square brackets after R means a number of rows above, a negative number in square brackets after R means a number of columns to the left.

If you want an absolute reference (the equivalent of the dollar signs before the Column/Row reference) then remove the square brackets and use a positive number. For example, to refer to the second cell down from the top row in the current column we use =R2C as a reference. If I switched back to normal referencing and was in a cell in column E this would show up in the formula bar as =E$2.

Using R1C1 Reference Style in VBA

When I am teaching Visual Basic for Applications (VBA) programming I encourage my students to use this style of referencing in creating formulas as it solves problems when trying to programmatically enter a formula and you don’t want determine which row the cell is in . For example, if my VBA code had just imported some data and I wanted to now place a simple SUM formula at the bottom of the data, what do I enter? Well, let us assume I am in the first blank row below the data and I am not converting this range of cells to a table.  Also the first row of data is a column heading/title so I only want to add up from row 2 downwards. The formula would be:

=SUM(R2C:R[-1]C)

Using VBA, let’s assume I am currently positioned in the cell where I want to place the formula, my code would be:

Activecell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"