/

Basics of Relative, Absolute and Mixed Reference in Calc

15642 views
1

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

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.

Relative Reference in Calc
Relative Reference in Calc

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
Absolute Reference in Calc
Absolute Reference in Calc

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.

Absolute - Relative toggle
Absolute – Relative toggle

This explains the basics of relative, absolute, and mixed references. Drop a comment below if this article helped you.

Reference

Join our Telegram channel and stay informed on the move.

arindam

Creator of libreofficehelp.com. The aim is to help as many people as possible with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
123

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?