If you create a lot of calculations within Microsoft Excel changing from the default of Relative Reference is a very useful tool. When you create a calculation but one of the references is static and you want to copy that calculation, the reference will automatically change relative to how it was copied.
So if you have a percentage in cell B2 and want to multiply the figures in Column A by the percent you would want the calculations to appear as follows:
=A4 * B2
=A5 * B2
=A6 * B2
If you typed =A4 * B2 into the first cell then copied it down the next cell would have =A5 * B3. This is because the cell references are relative, there is nothing to tell Microsoft Excel that the reference should not change.
Each cell reference is made up of 2 parts, the column and the row. So B2 is made up with the column B and the row 2. By adding the $ symbol to the part you want to stay static it will change the way the reference will act when copied. If I want the column to always be B then I would use the reference $B2 but if the reference is copied down a cell it will change to $B3. If I want the row to always be 2 then I would change the reference to B$2. These are examples of mixed references, if you copy them vertically or horizontally they will act differently. If you always want the entire reference to stay static and never change no matter where it is copied to then place the $ symbol in front of both parts, $B$2.
0 Response to "MS Excel - Absolute Reference Vs Relative Reference"
Post a Comment