One of the main topics in MS Excel is understanding the difference between Relative referencing and Absolute Referencing. It seems to confuse many people who often copy/cut and paste cells that contain formulae and functions.
Hopefully this article should be able to clarify the differences.
Normally when you enter a formula in a cell and try to copy it to other cells the cells mysteriously start to change on the formulae and we start getting confused!
Lets see an Example
On the image above you can see that the AutoSum feature has been used on cell B7 to calculate the sum of Jan Sales. In order to calculate the sales for Feb and Mar that formula was simply copied and pasted using the Fill Handle ( the dark dot at the Bottom Right hand corner of the active cell).
The original formulae on cells B7 is: Sum(B2:B6) and when it was copied over to the other cells the cells updated to Sum C2:C6 and D2: D6 for C7 and D7 respectively. This is something Excel does automatically and is known as Relative Referencing.
Lets say that we decided to give 5% discount on the total of the product sales!
On the above image you'll see that the discount rate of 5% is on Cell I1 and the formula on Cell F2 is =E2*I1
If you tried to copy and paste the formula to other cells using the fill handle you will get the result as 0 in all the other cells!
The reason for this is because when you copy the formula the cells use the relative referencing and E2 change to E3, E4, E5 and E6 but I1 also change to I2, I3, I4 and I5. Therefore, we need to Fix the I1 so that when we copy the formula Every cell i.e. E3, E4, E5 and E6 should multiply with I1.
In Excel to do this we need to input $ signs. So if we modified the formula to be =E2*$I$1 and copy and paste it to the other cells the I1 will remain constant (Fixed) as seen on the next image:
To conclude I would like to state that sometimes we take Relative Referencing for Granted and assume that it will always work for us when ever we copy and paste cells that contain formulae but as seen above it did work for us when we did AutoSum but it didn't work at all when we tried to calculate the Discount! We had to fix I1 by entering formulae. So please be very careful before copying and pasting any formulae and functions.
If you found this post to be helpful you'll definitely find the FREE Training Manuals on Mouse Training Website.
I hope you'll come back to this for more Excel Help in the near future!
