Leading the way in Microsoft Office Development

Excel

Step 1 - 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.

Step 2 - 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.

Step 3 - 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.

Step 4 - Comparing Notations

Step 5 - 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.

Step 6 - 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.