Excel Functions and Formulas – Referencing
Referencing cells correctly is very important in the design of formulas. When you refer existing data, you do not have to enter the cell name using keyboard. You just need to click on the particular cell; the cell name would be automatically entered. In fact, you SHOULD refer existing cells as much as possible. Then only, you will be able to make use of the benefits of formulas because you do not have to do anything even if you change values. Consider a simple example to illustrate this.
If you wish to calculate total marks, you can do it in two ways; you can use the formula ‘=(C3+C4+C5)’ or ‘=(46+38+40)’. Which one do you think the correct approach and why? Of course, the best option is the first one, that is referencing the cells rather than entering the values. The reason is if you make changes to marks of any of the subjects, you do not have to do any changes if you go by the first approach. But, if you go by the second method, then you need to change the formula also. There you cannot enjoy the benefits of formulas.
There are three types of referencing. They are relative, absolute and mixed referencing. Relative reference will change the row and column numbers if it is copied to other cells. To make it simpler, consider the same example of marks. Suppose you select the cell C7 and click Ctrl+C and then click Ctrl+V in cell D7 what will happen? If you see the formula bar, you could see that formula has been changed as ‘=D3+D4+D5’. But if you try to copy the same formula in D4, you will get an error. Why it happens so? The reason is that when you try to copy the formula, cell references are changed automatically in consistence with original formula in case of relative referencing.
So, the original formula adds values in the cells which are 2, 3 and 4 rows above the actual row that contains formula. When you try to copy the formula in 7th row, then it will try to add values in 5th(2 rows above 7th row), 4th (3 rows above 7th row) and 3rd (4 rows above 7th row)rows. But, when you try to copy the formula in D4, it would try to add values in 2nd (2 rows above 4th row), 1st (3 rows above 4th row)and 0th (4 rows above 4th row)row. As there is no 0th row, it would return an error. Excel uses relative addressing by default.
Absolute reference does not change even you copy the formula in any cell. In the above example, suppose you store a value 100 in the cell C2 and you always wish to add this value to the total marks. So, if you use relative referencing, it would make trouble in many situations. So, there you need to go for absolute reference. In case of absolute referencing, wherever you use the formula, the value in the cell C2 itself would be considered in the calculation. Absolute address is defined using $ sign. Instead of specifying as C2, you need to specify as $C$2 in the formula for using absolute referencing. You can either type $ sign using keyboard or you can click the cell reference which you wish to make absolute referenced and then click F4.
Mixed reference is half relative and half absolute. If you wish to change either column letter or row number to be fixed while doing operations, then you can go for mixed references. But, mixed references are not used commonly. For example, A1:A4 is relative reference, $A$1:$A$4 is absolute reference and A$1:$A4 or $A1:$A4 is mixed reference.
Read More about Excel here