The usage of cell addresses in the formula in the place of values is known as Cell Referencing. The reference of cell can be entered in three ways in a formula. They are:
- Relative
- Absolute or Constant
- Mixed
Relative Referencing
The name of the cell is the combination of column name and row number. When you entered the cell address without prefixing ‘$’ sign before any part of cell address it is known as Relative Referencing. This is of course the default one.
Eg: C4
Absolute or Constant Referencing
If the cell address is entered by prefixing ‘$’ sign before each part of its name i.e. column as well as row, then it is known as Absolute Referencing.
Eg: $C$4
Mixed Referencing
In this reference, you will prefix ‘$’ sign before one part of cell address.
Eg: $C4 (or) C$4
You will notice absolutely no difference in the final result of the formula when using any of the above references. Then what is the use of these different types of references?
The difference between these three types comes when you copy the formula from one cell to another. You can use Copy command or Fill Handle to do so.
What will happen when you copy a formula?
In the case of Relative Referencing, cell addresses in the formula changes or refers to some other cells, with respect to the new location into which formula is copied.
Eg: C4+D5
When the cells are referred in Absolute style, then the referred cell addresses won’t change while copying.
In Mixed referencing, the ‘$’ prefixed part of cell address remains constant while the other would change.
Changing between Cell references:
- Being in cell editing mode, select the cells to be changed
- Press F4
Using AutoFill with Formulas:
- Enter the formula in required cell
- Place the mouse pointer at bottom right corner of formula holding cell
- Drag down
(OR)
- Select the cell range for which formulas to be created
- Type the formula in one cell
- Press Ctrl+Enter
Showing the formula in the Cells: ctrl+ ~
Types of Errors:
#VALUE: Irrelevent data types are used in formula
#REF: The cell address referred in the formula is not available.
#####: The column width is not sufficient to the data
#NAME: Name of the function entered wrong
#DIV/0!: The denominator is entered as zero.