Relative Absolute Cell References in Excel

Relative and Absolute Cell References in Excel 2007
Relative, mixed and absolute cell references in Excel: Most of the time we use relative cell references when we perform actions like calculations in Excel. For example, in the training video you can observe that we multiply the unit price in cell b2 with the quantity in C2 and get the amount for the electrical item ‘Fan’. Then we click in cell D2, bring our mouse cursor to the bottom right of the active Excel cell and then drag to perform an auto-fill i. e. we don’t need to write the formula again and again and the formula gets copied ‘relative’ to the first calculation formula in cell D2. Now if we have different category products as in our example like ‘electrical’ and ‘electronic’ and these items have a different VAT then we cannot perform the calculation as we did with the ‘amount’ calculation. In fact, you can see that when we calculate VAT of the last item we get ‘0’ as result. This is because in the worksheet ‘VAT’ only two categories have been defined and the cell B4 has no value. Therefore when you autofill the formula in cell E4 you get ‘0’. Moreover, did you notice that the first item is electrical, the second and the third items are electronic? You need to access the right ‘VAT’ for the different categories. This is done through an absolute reference as shown in the training video. We access the VATs by using ‘VAT!$B$2’ for the electrical category and ‘VAT!$B$3’ for the electronic category. What is the meaning of VAT!$B$3? First we define the worksheet which has been named ‘VAT’, then we add ‘!’ and finally we use ‘$’ signs to access the B column and second row. We are telling Excel to always access this and only this cell! Absolute references can become very important in many situations: Let’s say the sales tax department revises the VAT or has revised the VAT and you were not aware of this situation, you only need to change the rate in the VAT Excel worksheet and your calculations will be immediately corrected. These kind of situations are often encountered in imports and exports in many countries where ‘duties’ keep on changing regularly! In such and many other situations the absolute reference feature can be a blessing!

Leave a Reply

Your email address will not be published. Required fields are marked *