Relative and Absolute Reference

There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells or when using the autofill feature of Calc.

• Relative references change when a formula is copied to another cell.
• Absolute references, remain constant no matter where they are copied.

Relative references

By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

Absolute references

There are occasions when you don't want a cell reference to change when copied to other cells. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant.

To create an absolute reference in a formula we add the dollar sign ($) before the column reference, the row reference, or both.

Let's see an example to better demonstrate the concept of absolute references. The spreadsheet shown in the image calculates the Tax using the formula

Tax = Line Total * Tax Rate = D3 * C8

If we use autofill to calculate the tax for the remaining items, then we see that on the second line the reference for the Tax Rate becomes C9. This leads to error in our formula because we want the Tax Rate cell C8 to remain constant.

For this reason we change the initial tax calculation formula to use absolute reference C$8 for the tax rate cell. In this way we get the correct results when we update with autofill the remaining cells.

In this example we used absolute reference for the row number (C$8). Alternatively we could use the full absolute reference using $C$8. In this case we autofill vertically and the column name does not change, therefore it's not necessary to use absolute reference for the column name.