LibreOffice Calc · Formula troubleshooting · Beginner
Absolute and Relative Cell References in LibreOffice Calc
Understand A1, $A$1, A$1 and $A1 in LibreOffice Calc. Copy formulas safely and lock the right parts of a reference.
Updated 10 October 2026 · Formula checks: testing standards
A copied spreadsheet formula sometimes changes a reference you wanted to keep fixed. In LibreOffice Calc, dollar signs let you lock a column, a row or both. Once you understand the four styles, many fill-down errors become easy to fix.
Four ways to reference A1
| Reference | When copied down | When copied right |
|---|---|---|
A1 |
Row changes | Column changes |
$A$1 |
Fixed | Fixed |
A$1 |
Row fixed | Column changes |
$A1 |
Row changes | Column fixed |
The $ locks the piece that follows it. This matters when you copy a formula—not just when you edit a value.
Example: apply the same tax rate to every row
Suppose B1 contains the numeric tax rate 0.13, and A2:A4 contains untaxed prices 10, 20 and 50. Put this formula in B2:
=A2*(1+$B$1)
Copy B2 down to B3 and B4. The price reference changes, but the tax rate keeps pointing to B1.
| Cell | Formula after filling | Expected result |
|---|---|---|
| B2 | =A2*(1+$B$1) |
11.30 |
| B3 | =A3*(1+$B$1) |
22.60 |
| B4 | =A4*(1+$B$1) |
56.50 |
With the incorrect formula =A2*(1+B1), copying down shifts the rate reference to B2, B3 and so on. Depending on where formulas are placed, that can even introduce an unintended circular reference.
Use F4 to switch reference modes
Select a cell reference within the formula bar and press F4 to cycle through relative and absolute addressing. The official Calc documentation describes the sequence A1 → $A$1 → A$1 → $A1 → A1. Some laptops need an Fn key combination.
More than one sheet
When pointing to another sheet, Calc uses a sheet reference such as $Sheet2.$B$1. Confirm that the reference is correct before filling a whole column, especially if imported formulas were written using different spreadsheet conventions.
Common questions
Does $A$1 prevent changing the value in A1? No. It controls how the formula’s reference behaves when copied.
Can I lock only the row? Yes: A$1. For a column only, use $A1.
Related: SUM and COUNTIFS with date boundaries.
Official reference: LibreOffice Help. This tutorial focuses on LibreOffice Calc; menu names and behavior can vary by version, operating system and locale.