1-Mar-2013

If you are fairly new to Excel getting to grips with relative and absolute references is a key lesson to making your Excel life simpler. It is only when you use Excel's autofill you will notice the difference between the two reference types.

To illustrate the difference, consider a fictional company that makes gadget products - amazingly always sold at the same price. Their projected sales figures are illustrated below.

To find out the total sales at each month, we can add a sum formula. We enter the formula for the first month in cell C10.

Then click on the grab handle at the bottom right of cell C10, and drag it across to cell E10. We now have the correct formula in each of the three total cells. The formula =sum(E3:E9) contains a range (E3:E9) of cells to be summed in relative reference format.

However there are often times when relative references give us the wrong result. For example, suppose the fictional company wants to see what happens to the revenue at different price points if the sales volumes are held constant. First we add in 3 price points.

Then we add the formula in cell C12, =C10*B12 which gives the total revenue for Jan as 330000 at a price point of 2.5.

However if we drag this formula down as it stands, we get some weird results. Checking the formula in cell C14, we can see it is not picking up the total from C10.

The solution is to fix it so that the row does not change when we autofill. To do this, put a dollar sign in the formula before the row which will make the row reference absolute rather than relative. That is =C10*B12 becomes C$10*B12.

Now when we drag down and autofill, the resulting revenue amounts are now correct.

However if we select cells C12:C14 and then drag them across, the result is still not right. Hopefully you can see that cell E14 is not picking up the price from B14 but a value from D14.

To fix this, starting again in cell C12, we use the formula =C$10*$B12 which has a dollar in front of the B indicating we want this to be an absolute reference.

Now when we drag the formula first down...

... and then across - or vice versa - we end up with the result we wanted.

We can of course fix both the row and column part of the address at the same time. This time we are going to put the price into one specific cell and see how the projected revenues for all the products change as we vary the price. To do this we put price in the cell G1. Then in cell G3, we enter the formula =C3*$G$1 which has both absolute row and column reference.

This is the result when we have autofilled by dragging down and across. The great thing is we can try different prices to see what happens to the revenue. Obviously as a business model it lacks some realism, but hopefully it has helped explain the difference between relative and absolute references.

Incidently if you want to quickly change the reference type, highlight the part of the formula you want to change in the formula bar and press F4, which will toggle it through the different reference types.

Screen shots created in Excel 2007 on Windows 7