Cell Addressing in LibreOffice Calc Spreadsheet

Concept of Cell Address (Row & Column) and selecting a Cell-

Any cell value is identified by the cell address. And cell address contains Row and Column. In LibreOffice Calc spreadsheet is used which is the actual working area and made up of thousands of rectangle, which are called cells. Or we can say that a cell is the intersection of a row and a Column.

In Calc, Column are identified by letters (A, B, C) While rows are identified by numbers (1,2,3).

Each cell has its own name –or cell address- based on its column and row.

In the example, the selected cell intersects column C and rows 2, so the cell address is C2.

The cell address will also appear in the name box.

Note that a cell’s Column and row headings are automatically highlighted when the cell is selected & displays in the Name Box.

You can also select multiple cells at the same time.

A group of cells is known as a cell range. Rather than a single cells address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, Separated by a colon (:). 

For example, a cell range that included cells A1, A2, A3, and A4 to A8 would be written as A1:A8.

Cell referencing-

Cell reference refers to the cell or range of cell in a spreadsheet. Each cell in a spreadsheet is named by its column and row labels. The row labels are numbers and the column labels are letters. The first cell, therefore, is called A1. One row down and one column over is cell B2, and so forth.

To reference the value stored in a single cell, enter its coordinates as a function arguments. For example, to have the data in cell B1 appear in another cell, enter =B1 into that cell.

There are following Types of cell references used in LibreOffice Calc-

Relative cell reference:

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

Absolute Cell reference:-

It is used 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.

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

A$2   The Row does not change

$A2   The Column does not change

Mixed Cell 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 not

Note:-  Relative and absolute references behave differently when copied and filled to other cells or when using the auto fill feature of Calc. Relative References change when a formula is copied to another cell. Absolute references remain constant no matter where they are copied.