Categories
Excel

R1C1 Reference Style

R1C1 reference is a way of referring to cells in Excel using numbers for both rows and columns. For example, cell A1 is R1C1, cell B2 is R2C2, and so on. R1C1 reference can be useful when you want to create formulas that use relative references.

  1. Switching A1 to R1C1
  2. Referring Cells using R1C1 Reference Style
  3. R1C1 Absolute References
  4. R1C1 Relative References
  5. R1C1 Mix References
  6. A1 Vs. R1C1 References

In Excel, you can refer to cells using two different styles: A1 and R1C1:

  • The A1 style uses letters for columns and numbers for rows, such as A1, B2, C3, etc.
  • The R1C1 style uses numbers for both columns and rows, such as R1C1, R2C2, R3C3, etc. The R stands for row and the C stands for column.
R1C1 References: The column letters changed to numbers.

Switching A1 Reference to R1C1 Reference

To change to R1C1 reference style:

  1. Choose File > Options
  2. Click the Formulas from the Options dialog box
  3. Check the R1C1 reference style option.

Referring to Cells with 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 mixed references. Try the following examples by typing the described notations in the 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, Excel will not move the active cell to a different location. Instead, it will keep the same cell selected.
  • RC1 refers to the same row and column 1. If your active cell is R5C4, typing RC1 in the Name Box will move the active cell to R5C1.
  • R1C refers to the same column and row 1. Typing R1C in the Name Box will move the active cell to the first row on the same column.
  • 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).

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 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 points to the active cell itself.
  • RC[1] The R without brackets indicate that the referenced cell and active cell are on the same row. C[1] indicates that the referenced cell is on the next 1st column (to the right) from the current cell.
  • R[1]C The R[1] indicates the referenced cell is on the next 1st row (down) from the current cell. The C without brackets mean the referenced cell and current cell are in the same 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 the left) from the current cell.

R1C1 Mix References

Relative Row and Absolute Column

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

Comparing A1 and R1C1 References

  • The A1 reference style is the default one and uses letters for columns and numbers for rows.
  • The R1C1 reference style uses numbers for both columns and rows and indicates the relative position of a cell from the active cell.
  • R1C1 reference can be useful when you want to create formulas that use relative references.
  • The A1 reference style is more intuitive and easier to use for most users.

The following table shows some examples to demonstrate a few basic differences between A1 and R1C1 references:

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
The active cell is C4 (or R4C3

Understanding Workbooks and Worksheets: