Page 320 - TP_IT_V1.0_C9_flipbpookl
P. 320
CELL REFERENCING
We know that a cell address is a combination of a column name and a row
number such as A5, B12, H23, etc. Using a cell address in a formula or a
function is known as a cell reference.
For example, writing =H10 in cell D6 will refer to the value in cell H10, i.e., the
value of D6 will be the value placed in cell H10. If any change in the value in cell
H10 occurs, then the value in cell D6 will be automatically updated.
There are three types of cell referencing which are as follows:
• Relative Referencing • Absolute Referencing • Mixed Referencing
Relative Referencing
Whenever a formula or function is copied to some other cell, the address in the formula or function changes relative
to the location where it is copied. This is known as relative referencing. In a spreadsheet, cell referencing by default is
relative cell referencing. For example, when a formula is copied downwards or upwards in the cell, then the change in
the row number relative to the location will occur. Similarly, if a formula is copied horizontally either to the left side or
right side, then the change in the column name with respect to the position will occur.
Let us study the given sheet. A formula for calculating the Profit or Loss is written in cell F9 as =E9-D9. If we copy this
formula two cells up (i.e., cell F7), then the row number will decrease by 2 and the new formula in cell F7 will be =E7-
D7. Similarly, if we copy this formula to two cells down in the same column (i.e., cell F11), then the formula will have
an increase of 2 in the row reference and the new formula in cell F11 will be =E11-D11.
Absolute Referencing
Sometimes, we do not want the address in the formula to change with respect to the location and we need to fix a cell.
Thus, if we copy the formula anywhere across a sheet, the cell address in the formula does not change. This fixing of a
cell in a formula/function so that its location does not change with respect to the location where it is copied, then this
type of referencing is known as absolute referencing. The dollar sign ($) is used in front of a column name and a row
number to make it an absolute cell reference, like $A$10, $F$12, etc.
Let us study the given sheet to understand the concept better. The cost price of all five cardboards are the same, so we
will fix the cell D7 by writing $ in front of the row number and column name to make an absolute reference as $D$7.
This cell will remain the same across the whole sheet in a cell reference.
318 Trackpad Information Technology (Ver. 1.0)-IX

