A cell reference is naming of a cell that can be used while calculating formulas and functions in Excel. There are two types of Cell References- Relative and Active.
Difference Between Relative and Active Cell References
The relative cell references change in relation to their location while the active cell references remain constant no matter where they are copied and pasted at.
Relative References
Relative References are those cell references that change when copied and pasted to some other location. For example, Madhur has the following data.
As shown, he calculates the total expense by adding his household and official expense.
Now, when he copies the same formula to the next cell that is D3, the cell references in the formula change according to the row.
Due to this property of relative cell reference, it now becomes easy for him to calculate the value over all the cells of column D. To do this, he has to select the cell that has the formula (in this case D2 or D3) and locate the fill handle on the bottom right corner. He can now drag using the fill handle to all the necessary rows giving him the result as follows.
Do note that all the cell references are relative by default in Excel.
Active Cell Reference
An active cell reference does not change its reference in relation to the cell it is in. For example, in active cell reference, the above formula would not change with each row.
To make a cell reference active from relative, he has to add the ‘dollar sign’ ($) before the cell reference.
Adding a dollar sign only before the alphabet (that is the column reference) will make the columns constant while the rows are relative.
For example, for $B2+$C2 the result will be as follows
By using a dollar sign before the numbers (that is row reference) and not the alphabets (that is column reference), will have the following result
=B$2+C$2
To make both the row and column references constant, the formula to be used is
=$B$2+$C$2
Let us take another set of data.
Midhun is running a tuition centre for which he has asked Rani to get him some children. Rani has successfully admitted the above children to his tuition centre. Her commission is 1500 per student. How can she calculate this?
To do this, she can use the formula =B2*$E$1 for the cell C2 and extend it for all the other cells using the fill handle. The result would look as follows.
