This beginner’s guide explains the concepts of relative, absolute, and mixed reference in LibreOffice Calc.
Table of Contents
Relative reference
Relative reference a pointer to a cell or a range. For example, if you want to refer to cell A1 via relative reference, use this:
=A1
Relative reference changes when copied to other cells in a worksheet because it has an offset number to another cell which is not fixed. By default in LibreOffice Calc, all references are by default are relative. However, you can convert it to absolute reference using '$' sign as explained in the next section.
Example of Relative reference
In the example shown above, the formula in D2 contains two relative references that will change as follows when copied down column D:
=C2*B2 =C3*B3 =C4*B4 =C5*B5
Absolute Reference
In contrast to relative reference, an absolute reference refers to a cell or a range that is “locked” and it won’t change when you copy or drag the Calc cell handle in consecutive cells or ranges.
An absolute reference refers to an actual fixed location in Calc.
To create an absolute reference in Calc, add a dollar ($) sign before the row and column identifier. For example, an absolute reference to A1 looks like this:
=$A$1
An absolute reference for the range A1:A10 looks like this:
=$A$1:$A$10
Example of Absolute Reference
In the above temperature conversion example, where centigrade is converted to ferenheit – the cell B2 contains the absolute reference of the conversion formula / factor of this calculation. Hence when you copy down the formula from B2 to B5, the first part A2 changes but the absolute reference remains same for all.
Mixed Reference
Mixed reference in Calc is a reference where both absolute and relative reference exists. For example, the following references have both relative and absolute components:
=$A1 // column locked; A column locked but the row varies
=A$1 // row locked; Column A varies but row is fixed
=$A$1:A2 // first cell locked; but A2 can vary
Mixed references can be used to set up formulas that can be copied across rows or columns without the need for manual editing. Mixed reference is a bit tricky to set up initially in your worksheet, but they are very useful. Mixed reference can easily be copied to other cells without any manual formula modification and it reduces manual or typo errors. The more you use mixed reference, the more you can make your worksheet robust and automated.
Usage Notes
You can toggle between relative and absolute reference using F4. This is much faster when you are working with a huge set of worksheets and formulas. Just hit F2 to enter into the edit mode of a cell and keep pressing F4.
This explains the basics of relative, absolute, and mixed references. Drop a comment below if this article helped you.
Very well explained. But I was searching of a different topic: I don’t change the cell with the reference. But the target is changed. E.g. Reference of cell C2. Insert coloumn / cells before. Reference is updated automatically to D2. But I want to have the reference to C2 on any circumstance. It that possible as well?