How to use Relative and Absolute References in Excel?
If you are new here, Subscribe to Full Feeds or by Email or follow us in Twitter and receive free daily updates on Office and Windows Tips, Tricks and Tweaks..
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.
[...] Note: F4 is a Shortcut Key to Repeat the last action in Excel and also it is used switch between absolute and relative reference in Formula. [...]
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…
[...] or by Email and receive free daily updates on Tips, Tricks and Tweaks.. In my last post about Relative and Absolute Reference in Formulas, I showed the formulas in all the cells instead of the values. Formulas in Excel are the best [...]
[...] and Tweaks.. Lately I have been writing about using formulas and about different kind of cell referencing in Microsoft Excel. The most commonly used formula in any spreadsheet depending upon various [...]
[...] Though relative referencing is used by default in R1C1 reference mode, one can also use absolute reference with R1C1. For the same example as above, with absolute reference, the range would be referred as R1C1:R5C1. To know more about referencing, read How to use relative and absolute reference in Excel? [...]
[...] referenced rows or columns along with the rows or columns from where it is being referred. In both absolute and relative reference, the values it is being referred will not be referred [...]
Hi,
I was googling “excel 2007 absolute cell shortcut” to try figure out what is replacing the old lovely F4 and found your article.
I believe you know how to do it, Haven’t found the answer yet.
The new excel 2007 doesnt have a shorcut key for that matter! and it is so useful – I even tried surfing the macros to make a button out it – but nada!
any idea?
This is such a powerful shorcut which is totally lost,
Thank you in advance,
Shabi
I tried to use the reference cell for comparing two columns, however, if I find a value that does not match I insert a cell to shift the column down. When I do, the cells changes…for eg, $B11=D12 instead of $B11=D11. Is there a way I can use either absolute or relative reference for this kind of comparison?
Hi,i was trying to get my excel practical done but i got stuck in a part with absolute and relative referencing.The step was to use a formula with both of these and calculate the sum of a particular thing in the table using this formula.All i want to know is which formula these guys are talking about??