Absolute vs Relative References in LibreOffice Calc

In LibreOffice Calc, understanding how cell references behave is essential for building accurate and dynamic spreadsheets. This article explains the fundamental differences between relative and absolute cell references, how the dollar sign ($) modifier locks cell coordinates, how mixed references work, and how to switch between these reference types to streamline your formula creation.

Relative Cell References

A relative reference is the default reference type in LibreOffice Calc. When you write a formula referencing a cell (such as =A1 + B1) and copy or drag it across rows or columns, the reference coordinates automatically adjust based on their relative distance from the new formula location.

Syntax Example: A1
Behavior: If you write =A1*2 in cell B1 and drag it down to B2, the formula automatically changes to =A2*2. If you drag it right to C1, the formula adjusts to =B1*2.
Primary Use Case: Calculating repetitive, row-by-row or column-by-column values, such as item subtotals or row-level additions.

Absolute Cell References

An absolute cell reference points to a fixed cell address. When you copy or autofill a formula containing absolute references, the referenced cell coordinates remain unchanged, regardless of where the formula is moved.

Syntax Example: $A$1
Behavior: The dollar sign ($) before the column letter locks the column, and the dollar sign before the row number locks the row. If you enter =$A$1*B1 into cell C1 and drag it down to C2, the formula becomes =$A$1*B2.
Primary Use Case: Referencing static values that apply to multiple calculations, such as a fixed tax rate, a discount percentage, or a universal currency conversion factor.

Mixed Cell References

Mixed references lock either only the column or only the row, leaving the other dimension relative.

Locked Column ($A1): The column remains A when copied horizontally, but the row number changes when copied vertically.
Locked Row (A$1): The row remains 1 when copied vertically, but the column letter changes when copied horizontally.
Primary Use Case: Building multiplication tables or complex matrix grids where formulas must simultaneously reference a static header row and a static index column.

Key Differences at a Glance

Feature	Relative Reference (`A1`)	Absolute Reference (`$A$1`)
Syntax	No dollar signs (`A1`)	Dollar signs on both coordinates (`$A$1`)
Copying Behavior	Adjusts relative to the new cell position	Remains permanently fixed to the target cell
Default Setting	Yes	No
Typical Application	Line-item totals, dynamic sequences	Fixed rates, lookup tables, constant variables

How to Switch Reference Types in LibreOffice Calc

To quickly toggle between relative, absolute, and mixed references without manually typing dollar signs:

Double-click the cell to edit the formula, or select it and click in the Formula Bar.
Place your cursor on or directly next to the cell reference you want to change.
Press Shift + F4 on your keyboard to cycle through the available reference types: A1 → $A$1 → A$1 → $A1 → A1.