Basics of Relative, Absolute and Mixed Reference in Calc

This beginner’s guide explains the concepts of relative, absolute, and mixed reference in LibreOffice Calc. 

Relative references

Relative reference is 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 references

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 References

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 References

In the above temperature conversion example, where centigrade is converted to Fahrenheit – 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 the same for all.

Mixed References

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 references are a bit tricky to set up initially in your worksheet, but they are very useful. Mixed references can easily be copied to other cells without any manual formula modification, and it reduces manual or typo errors. The more you use mixed references, the more you can make your worksheet robust and automated.

Usage Notes

You can toggle between relative and absolute references 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.

Reference