In my last post on Excel Tips, I mentioned that the shortcut key F4 is also used to insert Absolute Reference while inserting formulas in Excel apart from using that shortcut to repeat the last action.
So here is a tutorial on how to use relative and absolute references in Excel.
First what are relative and absolute references?
Relative Reference:
If you are using formulas quite often in Excel 2007 or earlier versions, most often these formulas are repeatable or extendable to other rows or columns which can be done by auto fill. For example, (from the figure above) lets say the formula to calculate the expenses in week A is =SUM(B5:B8). This can be extended to other columns following it, so its not necessary to rewrite the formula again and we can just auto fill this series (left click when the cursor is on the edge of the cell and then drag outside to extend series or fill). While extending here, the formulas are adjusted automatically to give reference to the corresponding columns. This is called relative reference.
Absolute Reference:
But while calculating the balance (again from the example in figure above), the total is always deducted from the constant cell value in all cases. If we write the formula =B2-B10 and extend it to the following columns, this will give wrong references like =C2-C10 and =D2-D10. So to avoid this, we use the absolute reference =$B$2-B10. Now when we extend, the formulas are correctly filled as =$B$2-C10 and =$B$2-D10. The $ symbol represents the absolute reference. So $B$2 is the absolute value of the cell B2.
The reference style used here are A1 Style but you can also use absolute and relative reference in R1C1 reference style.
In both relative reference and absolute reference, the references will be automatically updated if rows or columns are inserted before them.
Keyboard Shortcut to insert a absolute reference.
When writing a formula, use the function key F4 to change the reference from relative to absolute and vice versa.
Also see how you can display all the formulas in Excel, so its easier to review the Excel Sheet.
I’m no expert, but it seems to me that if you wanted a running total of remainding funds that you would need to write the formula as $B$2-B10 not the other way around as stated. This would result in a misleading negative number rather than the positive number. At least using the given numbers.
@Bruce, Thanks for pointing it out. I was concentrating on trying to explain the concept and missed out the calculation part. Will update it…