Cell referencing

M1-R5.1 & CCC · Chapter 4: Spreadsheet (Libre-Office Calc) · 3 min read

Cell referencing

Cell reference refers to the cell or range of cell in a spreadsheet. 

                                      OR 

A worksheet is created by rows and columns. Division of each row and column creates a cell, Each cell has a unique address. This address is called cell reference.The reference of a cell is following three types: 

  1. Relative Address 
  2. Absolute Address
  3. Mixed Address 

Relative Address:

  •  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 Address: 

  • Absolute references do not change when copied or filled.
  • You can use an absolut It is used when you don't want a cell reference to change when copied to other cells.
  • Unlike relative 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. 

$A$2    The column and row do not change when copied

A$2           The row does not change

$A2           The column does not change

Mixed Cell reference: 

Mixed cell 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