Absolute vs. Relative References

What are absolute and relative references and why should I care?

Well, first let’s answer what they are.  Have you ever seen an excel formula with a dollar sign ($) in it?  That’s an absolute reference.  It means that the reference to the column or row it’s in front of will never change no matter where you place the formula.

For example, if I have a formula reference set to =$A$2 then no matter where I copy the formula to it will always be equaled to the value in A2.  Now if I had =$A2 and I copied the formula to cell D6 then the formula would change to =$A6.  Why does this happen?  It’s because the absolute reference is for the column only and not the row.  So it will always reference column A, but the row will change to the row of wherever the formula is being copied.

If you read my article on Handy Excel Shortcuts you’ll know that you can quickly and easily add the dollar signs ($) without typing them.  When you’re entering the formula, click F4 when you’re on a cell reference and you’ll see the dollar signs added.  Click F4 again to toggle through the various reference options.  There are four references each listed below with a brief explanation.


Four types of cell references:

=$A$2 – Absolute reference, nothing will change when the formula is moved, it will always be equaled to the value in cell A2

=A$2 – Absolute row reference, only the column will change when the formula is moved

=$A2 – Absolute column reference, only the row will change when the formula is moved

=A2 – Relative reference, both the column and row will change when the formula is moved

So, why should I care?  Suppose you use a standard vlookup formula, something like =VLOOKUP(A2,B2:B10,1,FALSE).  This is stating that you’re looking up the value in A2 in the range B2:B10 and returning the value if it’s an exact match.  Now let’s consider you want to lookup the values in A2:A10 and see the matching values in B2:B10.  If you drag the formula down then you’ll have some unexpected results, because the reference is relative and not absolute (no dollar signs).  Once you drag the formula down it becomes  =VLOOKUP(A3,B3:B11,1,FALSE), then if you continue dragging down it will become  =VLOOKUP(A4,B4:B12,1,FALSE), etc.  You can quickly see that the range you’re trying to look in (B2:B10) is no longer the range you actually are looking in.  This can become a big problem.

Suppose you are looking up financial information on which customers accounts are paid in full.  If you don’t setup the formula properly with the correct reference types, you could be sending overdue notices to customers that have already paid.

Have a look at the example below, it illustrates the trouble you can get into if you don’t properly reference your ranges.

If you've any question, problem, suggestion and feedback than please comment below.

Have a nice day!