Microsoft Office Development and Consultancy
 Home|

Excel

|VBA|C#|Finance|Tools|Newsletter|Feedback|Contact 
 Excel > Formulas > A1 or R1C1 Notation< Previous | Next > 

 

What is A1 Notation ?

 
 

This is the default method used for creating cell references to other cells.

 
 

To refer to a cell, enter the column letter followed by the row number, for example "=B2" to refer to the cell which is the intersection of column "B" with row "2".

 
 

Cell addresses consist of a column letter and arrow number. Absolute references have letters and numbers. Relative references have a dollar in front of the letter or number.

 
   

 

What is R1C1 Notation ?

 
 

This is another way to create cell references which uses numbers for both the rows and columns.

 
 

Cell References are displayed in terms of their relationship to the cell that contains the formula rather than their actual position in the grid.

 
 

Cells are referred to by relative notation. Absolute references have numbers. Relative references have numbers in square brackets.

 
 

The above formulas will be changed to the following when you switch to R1C1 notation.

 
   
 

Negative row numbers mean that the referenced cell is above the cell containing the formula.

 
 

Negative column numbers mean that the referenced cell is to the left of the cell containing the formula.

 
 

Changing to R1C1 notation will change all the formulas in that workbook.

 

 

Switching to R1C1 Notation

 
 

You can change your cell references to the R1C1 notation from the (Tools > Options) dialog box.

 
 

Select the "General tab" and select the "R1C1 Reference Style" checkbox.

 
   
 

It is important to remember that changing this option will change all the formulas in the active workbook.

 

 

There are two different types of R1C1 Notation. You can have relative references or absolute references.

 
 

Relative References ( R[2]C[2] ) are the default and these always include square brackets around the numbers.

 
   

 

Absolute References ( R2C2 ) are do not include square brackets around the numbers.

 
   

 

Comparing Notations

 
 
In CellA1 FormulaR1C1 Formula
A1=B2=R[1]C[1]
A2=B2+1=RC[1]+1
A3=A2+1=R[-1]C+1
B4=$A$3+1=R3C1+1
C5=B$4+1=R4C[-1]+1
D6=$C5+1=R[-1]C3+1
A6=SUM(A1:A5)=SUM(R[-5]C:R[-1]C)
 

 

Advantages of R1C1 Notation

 
 

Although this is an older style of referencing cells it can be very useful for checking and finding any erroneous formulas.

 
 

This notation can be useful when you are more interested in the relative position of a cell rather than in its absolute position.

 

 

Things to Remember

 
 
  • In R1C1 style, both columns and rows are labelled numerically and the references are enclose in square brackets

     
     
  • References to cells and ranges do not have to be in the same sheet as the formula, nor even in the same workbook.

     
     
  • While the R1C1 references are less compact the numerical references make it easier for Excel to calculate row and column offsets used in macros.

     
     
  • When you copy a formula, every copied formula will have exactly the same R1C1 notation.

     

     © Better Solutions Limited 06-Dec-2013< Previous | Top | Next >