Excel 2019

R1C1 Reference Style

We normally view Excel worksheets with A1 style reference. In A1 style, rows headed from the top as numbers 1,2,3 and continuing downward and columns headed from the left as letters A,B,C and continuing to the right. Each cell address consists of a column letter and a row number. For example, the top-left cell address seen as cell A1 and the cell immediately below is A2 and cell to the right is B2 and so on.

Along with A1 style references Excel also supports R1C1 reference style. In R1C1 style, Excel use numbers 1,2,3 for both columns and rows. The cell A1 is referred to as cell R1C1, cell A2 as R2C1, cell B1 as R1C2, cell B2 as R2C2 and so on. The R1C1 uses R for row and C for column:

R1C1 Reference Style
The column letters all change to numbers.

  1. A1 to R1C1
  2. A1 Vs. R1C1
  3. R1C1 Absolute References
  4. R1C1 Relative References
  5. R1C1 Mix References

A1 to R1C1

To change to R1C1 reference style, choose File > Options and click the Formulas from the Options dialog box and place a check mark next to the R1C1 reference style option:

Options for R1C1 Reference Style

In R1C1, when you refer to a cell it creates the address of referred cell using its distance from the active cell. The R1C1 reference style can have relative, absolute or mix references. Try following examples by typing the following notations in the Name Box Name Box:

  • RC refers to the same row and column number as where the cell itself is located, when you type RC in Name Box the active cell not changed.
  • RC1 refers to the same row and columns 1
  • R1C refers to the same column and row 1
  • R1C1 refers to a cell in row 1 and column 1 (absolute reference, which is $A$1 for A1 reference)
  • R[-1]C[1] refers to 1 row up and 1 column to the right of where the active cell is located (relative reference).
  • R[1]C[-1] refers to 1 row down and 1 column to the left of where the active cell is located (relative reference).

A1 Vs. R1C1

The following table showing us some examples to demonstrate few basic differences between A1 and R1C1 references (Note: the active cell is C4 (or R4C3):

A1 Reference StyleR1C1 Reference StyleReference Type
A1R[-3]C[-2]Relative
$A$1R1C1Absolute
$A1R[-3]C1Absolute column and relative row
A$1R1C[-2]Absolute row and relative column

R1C1 Ref

R1C1 Absolute References

A1 reference style uses dollar sign $ for absolute cell references. The R1C1 reference style doesn’t use any special character for the absolute references. The R8C4 is an absolute reference which refers to a cell located on row 8 column 4 (for A1 style reference it would be $D$8) R1C2 refers to a cell located row 1 and column 2 and so on:

R1C1 Absolute Reference

R1C1 Relative References

Excel uses brackets [ ] to make a R1C1 relative reference and the number inside the brackets tell us the location of referenced cell from the active cell. For example:

  • RC
    Its point to active cell itself
  • RC[1]
    The R without brackets indicate that the referenced cell and active cell are on same row. C[1] indicates that the referenced cell is on next 1st column (to the right) from the current cell.
  • R[1]C
    The R[1] indicates the referenced cell is on next 1st row (down) from the current cell. The C without brackets means the referenced cell and current cell are on column.
  • R[1]C[1]
    Next row and next column from the current cell.
  • R[-1]C[-1]
    R[-1] points to the previous row (upwards) and C[-1] means the previous column (to left) from the current cell.

R1C1 Relative Reference

R1C1 Mix References

A mix referenced cell uses a “relative row and absolute column” or a “relative column and absolute row” reference. For example, if the active cell R8C4, the mixed reference to R1C1 would be R[-7]C1 (relative row) or R1C[-3] (relative column).

Relative Row and Absolute Column
Relative Row and Absolute Column

Relative Row and Absolute Column
Relative Row and Absolute Column

Advertisement:
Advertisement: