Excel Reference Styles
Excel understands two different styles of referencing for cells and ranges. These are described below:
A1-Style References
The A1-style is the most common form of Excel referencing, and is the default style.
This style of referencing is made up of a letter and a number, which represent the column reference and the row number, respectively.
Cells A1-D4 are labelled in the spreadsheet below:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | A1 | B1 | C1 | D1 |
| 2 | A2 | B2 | C2 | D2 |
| 3 | A3 | B3 | C3 | D3 |
| 4 | A4 | B4 | C4 | D4 |
R1C1-Style References
The R1C1-style of referencing is made up the letter R followed by a row number and the letter C followed by a column number.
Cells R1C1-R4C4 are labelled in the spreadsheet below:
| 1 | 2 | 3 | 4 | |
|---|---|---|---|---|
| 1 | R1C1 | R1C2 | R1C3 | R1C4 |
| 2 | R2C1 | R2C2 | R2C3 | R2C4 |
| 3 | R3C1 | R3C2 | R3C3 | R3C4 |
| 4 | R4C1 | R4C2 | R4C3 | R4C4 |
R1C1 referencing also allows you to refer to a cell that is a number of rows or columns relative to the current cell. This is specified by encasing the numeric part of the reference in square brackets.
If the row or column number is omitted, this tells Excel to use the current row or column number.
For example, if the current cell is R3C3 (or A1-style cell C3), then:
| R[2]C[2] | - |
refers to cell R5C5 (or A1-style cell E5) (add 2 rows and 2 columns to current cell) |
| RC[-2] | - |
refers to cell R3C1 (or A1-style cell A3) (use current row; subtract 2 columns from current cell) |
| R[2]C1 | - |
refers to cell R5C1 (or A1-style cell A5) (add 2 rows to current cell; use absolute column 1) |
This is illustrated in the spreadsheet below:
The R1C1 referencing style Option
Note that you need to tell Excel which style of referencing you are using. This is set by checking, or unchecking, the option R1C1 referencing style in the Excel options menu.
This option is found:
In current versions of Excel (Excel 2010 and later):
- In the File menu, under Options→Formulas
In Excel 2007:
- In the main Excel menu (accessed by clicking on the Excel Logo on the top left of the spreadsheet), under Excel Options→Formulas
In Excel 2003:
- In the Tools drop-down menu, under Options→General
By default, Excel uses the A1 style of referencing, and the columns of your spreadsheet are labelled with letters. However, if you select the R1C1 option, you will notice that the labels at the top of your Worksheet columns display numbers, instead of letters.