Row and Column cell references (R1C1)

Excel normally displays cell references in the A1 style i.e with rows each given a number and columns given an alphabetic character.

Absolute or Relative cell references determine whether ranges are incremented as they are copied to other cells. Placing the dollar symbol ($) in front of a column letter or row number will fix it if the formula is copied to other cells. If the relative formula SUM(A5:A10) is copied to the adjacent cell, it will update to SUM(B5:B10). The absolute formula SUM($A$5:$A$10) will refer to the same range, regardless of where it is copied to.

Excel can also handle cell references using a different format or notation. The R1C1 notation describes a cell location in terms of a row number and a column number. It can also distinguish between absolute and relative references. R5C1 is an absolute reference (for cell $A$5) whereas R[4]C[1] is four rows below and one column to the right, relative to the current cell.

In the above example the formulas in Cells C9, D9 and E9 are shown in both A1 and R1C1 notation. The first formula contains absolute references. The next two are relative formulas. Note however that in R1C1 format, these two are identical.

Select File | Options and Formulas to modify the worksheet appearance and display references as R1C1. Formulas change and column labels will switch from letters to numbers.

Use Excel options to toggle the Style		Worksheet with R1C1 references ON

These alternative R1C1 references can be applied using the Indirect function without switching Excel's formula properties. e.g. =SUM(INDIRECT("R[-5]C[0]:R[-1]C[0]",0)) will sum the five cells above the current cell. Note that an extra argument must be supplied to the Indirect function. =INDIRECT(TextRng, Type_TF). If TextRng is an R1C1 reference, the optional Type_TF argument must be set to FALSE or zero. If it is any other value or omitted the TextRng is assumed to be in normal A1 notation.

The next example uses the Indirect function to read the R1C1 style reference in cell F1. That string in turn reflects the month number in cell D2. The reference currently represents the first 3 columns of values on the current row. The nature of R1C1 references means that the exact same range string can be applied (using INDIRECT) against all of the cells in G4:G7. With the A1 notation a different INDIRECT reference would be required for each row. This example has been created to demonstrate INDIRECT() and R1C1 references. If I was trying to construct a table like this with dynamic formulas I would probably use INDEX() or OFFSET() in preference to this method.

In the following (embedded web app) example you can edit the month number value in cell D3 and thereby change the R1C1 string in cell F1. This string is used in each row in column G to supply the INDIRECT function with a dynamic range.

R1C1 references are most useful when writing Visual Basic code. If you wish to use VBA to write cell formulas, it is much easier to write code to increment 1, 2, 3 rather then A, B, C. Also, a single R1C1 formula with relative references can be copied (using VBA) to a range of cells and will adjust to the appropriate rows and columns.

The following VBA function can simplify the creation of the R1C1 reference strings. The user defined function has 8 arguments. FormRow and FormCol represent the cell containing the formula; Row1 and Col1 are the top of the range; Row2 and Col2 are the bottom of the range; AbsRef1 and AbsRef2 contain either 0 for a relative reference or 1 for an absolute reference in each half of the range. (This is for creating ranges within formulas and not for selecting ranges within a program.)

Function RC_Ref(FormRow As Integer, FormCol As Integer, Row1 As Integer, Col1 As Integer, Row2 As Integer, Col2 As Integer, AbsRef1 As Integer, AbsRef2 As Integer) As String ' ** create a text string for an absolute or relative range reference R1C1:R[2]C[2} Dim Temp1 As String, Temp2 As String If AbsRef1 = 0 Then Temp1 = "R[" & Row1 - FormRow & "]C[" & Col1 - FormCol & "]" Else Temp1 = "R" & Row1 & "C" & Col1 End If If AbsRef2 = 0 Then Temp2 = "R[" & Row2 - FormRow & "]C[" & Col2 - FormCol & "]" Else Temp2 = "R" & Row2 & "C" & Col2 End If RC_Ref = Temp1 & ":" & Temp2 End Function

This function can then be incorporated within other VBA code to supply the range references to formulas (in cells E28:P28).

This page is not included in the Excel Functions guide

Click here for details about obtaining this file