|
Relative 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
unitprice 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 autofill 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 electronical? 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!
|
