Let’s continue our discussion of uses of referencing in Excel. The illustration from the previous day is reproduced below for convenience.
As we discussed yesterday, cell C2 contains the mathematical expression =B2*30% i.e. Tax payable for the month has been calculated at the rate of 30% of the Gross Income of the month. Until now, we have only calculated the tax payable and Net Income for the month of January-2017. We, offcourse, need to calculate them for all through December-2017.
Do we need to individually type out the mathematical expression in all the cells pertaining to Tax and Net Income for every month? No, it is here that referencing come to our rescue.
Please copy the contents of the cell C2 and paste it in C3 and observe the mathematical expression and its result in cell C3.
I have reproduced the same below:
As you observe in the formula bar, the mathematical expression in cell C3 is not the same as it had been in cell C2. However, it has changed from =B2*30% to =B3*30%. That is, while the mathematical expression in C2 was calculating 30% of B2, the mathematical expression in cell C3 is calculating 30% of B3. Or, while C2 was calculating 30% of the Gross Income for January-2017, C3 is calculating 30% of the Gross Income for February-2017.
This automatic update of the mathematical expression as per location is possible because of referencing. If we had used the mathematical expression =82457*30% instead of =B2*30%, such an update would not have been possible. This type of referencing where the formula/mathematical expression updates itself automatically based on location is called as relative referencing
Relative referencing makes it possible to use a formula/mathematical expression repeatedly for similar calculations on different cells. In our example, we need to write the mathematical expressions used in C2 and D2, only once. After that, we just need to copy the mathematical expression in C2, to the cells C3 to C13 and mathematical expression in cell D2, to the cells D3 to D13.
Note 1: An easy way to copy and paste is to use the shortcut keys Ctrl + C and Ctrl + V. First, go to the cell to be copied and press Ctrl key and C. Excel changes the boundaries of the cell to a flashing dotted boundary to indicate that the contents have been copied. Then, go to the target cell and press Ctrl key and V to paste the contents copied from the previous cell. The memory used by Excel to store anything which is copied is called CLIPBOARD So, while Ctrl+C copies the contents of the current cell to the CLIPBOARD, Ctrl+V pastes the contents of the CLIPBOARD to the current cell)
Note 2: It is not even essential to copy the mathematical expression from C2 and then paste it in the cells C3 through C13. A quick way of copying a formula/mathematical expression down a range of cells is to use the fill handle. Please refer to the first image of this lesson. The small rectangle at the bottom right corner of the Active Cell is called fill handle. If you move your mouse to this small rectangle, the mouse pointer turns into a “+” sign. Once your mouse pointer has turned into “+”, double click the left mouse button to copy down the formula/mathematical expression up to the end of the data.
Please try out the concepts discussed in this instalment on your personal copy of Excel to have a better understanding.
