R1C1 Reference Syntax for Calc Formulas
Calc supports both R1C1 and the more widely used A1 notation for cell references.
The R1C1 notation is enabled in LibreOffice Calc options.
The R1C1 notation
In the A1 notation, columns are labeled with letters and rows with numbers, whereas the R1C1 notation uses numbers for both columns and rows.
In R1C1 notation, LibreOffice Calc displays columns headers as numbers.
Therefore, the cell A1 correspond to R1C1, which means row 1 and column 1, and cell A2 correspond to R2C1, that is, row 2 column 1.
The real difference comes when writing formulas and use a reference to other cells.
In R1C1 notation, cell references are based on their position relative to the active cell.
For example, if you reference cell B5 from cell A1, the R1C1 notation displays it as R[4]C[1], indicating four rows down and one column to the right of the active cell.
However, if the active cell is C3, the same cell (B5) is referenced as R[2]C[-1], meaning two rows down and one column to the left.
Use of brackets in R1C1 notation
Brackets indicate the relative offset from the active cell.
Positive numbers in brackets represent an offset to the right for columns and downward for rows.
Negative numbers in brackets represent an offset to the left for columns and upward for rows.
If there is no offset for a row or column (i.e., the offset is zero), the zero is omitted in R1C1 notation. For example, a reference to a cell in the same row but two columns to the left is written as RC[-2].
Absolute and relative references in R1C1 notation
Relative and absolute references are preserved in the R1C1 notation. The table below summarizes the equivalence. The current cell is R3C3 (C3)
|
A1 notation |
R1C1 notation |
Type of reference |
|---|---|---|
|
A1 |
R[-2]C[-2] |
Relative reference |
|
$A1 |
R[-2]C1 |
Relative row and absolute column |
|
A$1 |
R1C[-2] |
Absolute row and relative column |
|
$A$1 |
R1C1 |
Absolute reference |
Range expressions in R1C1 notation
As in A1 notation, the range operators work the same way. For example, a formula in cell R1C1 that sums rows 1 to 10 in column 3 is written as =SUM(RC[2]:R[9]C[2]).
To calculate the sum of all values in columns 4 to 7, write =SUM(C[3]:C[6]) in R2C1.
