When working with lots of data and formulas, understanding relative and absolute references is key to success in Excel.
First of all, does this look familiar?
Everything works fine……until you try to work out the VAT based on the value in cell A1.
So why does this happen?
By default, when you copy formulas in Excel the references are set to RELATIVE. By that I mean that when you copy a formula down 1 row, all the cells the formula is looking at also move the same distance. If you are not sure by what I mean, try this….
Select a handful of cells scattered across your screen and give them some colour so they stand out.
Now, enter a formula in a blank cell that adds up the cells you have just coloured in.
And press enter. The answer will be 0, but that doesn’t matter. Make sure your formula cell is selected, then click in the formula bar above the worksheet area.
When you do this each cell that is in the formula will get a coloured border that matches the colours of the cell references in the formula bar.
Now for the bit that will show us what happens when we copy a formula…
Click on the bottom right hand corner of the formula cell and drag it down 1 row. This will copy your formula down. Again, make sure that the new formula cell is selected and then click again in the formula bar. What do you see?
As you can see, the formula has moved down one row and so have all the formula reference cells. They have moved the same RELATIVE distance as the formula cell. You can repeat this in any direction you like to see what happens.
So now that you can see what happens when you copy a formula let’s go back to the original problem.
The first thing people do when they see errors appear is undo, delete, scream…or any combination of all of those. DON’T! Use the error messages to your advantage. If you delete the errors you have nothing to investigate or work with to try and find out what has gone wrong. Use the same steps as we have just used to see what happens when you copy a formula down or across. Click in the first cell where the formula works, then try the next cell and so on and so forth. Each time, click in the formula bar to see which cells the formula is looking at.
Now you can clearly see that the formula is looking correctly at £1.84, the net cost, but it has moved away from the 20% value in A1. You can then try out the cell that has the #VALUE error in it and see what that is looking at.
So remember: leave errors in place and use them to work out what has gone wrong.
So now to fix the problem.
We need to fix the position of the cell A1 so that when the formula is copied in does not away from it. You may have noticed in spreadsheets you receive from colleagues that some formulas contain cell references that look like $D$5. The dollar symbols represent ABSOLUTE referencing. In other words, the cell reference is fixed. A dollar symbol in front of the column letter means that the column is fixed, and the symbol in front of the row number means the row is also fixed.
You can type the ‘$’ symbols in if you like, but that is a bit of a faff, so the simplest way to do it is press the F4 key once you have selected the cell in your formula.
So in this example our formula should read;
So now when you copy your formula down, or in any direction for that matter, you will always be looking at the fixed cell location of A1.
Now…just to complicate matters you can also have PARTIAL ABSOLUTE references. When you press F4 is puts $ in front of both the column and row references. If you press F4 again it then displays A$1 which fixes the row number but turns the column into a relative reference. Press F4 again and then you get $A1, fixing the column reference but making the row relative. Press once more and you return to a fully relative cell reference.
What’s the difference between those then?
Let’s extend our example a bit. Now we have a total in GBP we now want to convert to USD and EUR, for which we have rates in cells G2 and H2 respectively.
I could write a formula in G4 fixing the Dollar conversion rate cell and then fixing the Euro rate in the next column. Might be OK to do for a couple of columns, but not if you have lots to do. So, we can create a formula that when copied across and down looks at the correct conversion rate every time whether we copy across and/or down.
In this example I want to be able to copy across so I cannot fix the column reference as I have a formula in column H too. However, when I copy the formula down I do not want to move away from row 2 in any of my columns as this is where the conversion value sits, so this needs to be fixed. This then gives us the formulas as shown below. This allows you to create a single formula in cell G4 which can be successfully copied across and down whilst still looking at the correct cell references.
If you want to practice using partial absolute references try this;
Create a grid to work out your times tables.
Enter a formula in the 1 x 1 cell. Then copy it across and then down to fill the whole table. Does it work? If not, try out various combinations of RELATIVE, ABSOLUTE and PARTIAL ABSOLUTE references in that single cell until you get the table correctly working out all the values. It may take some time, and you’ll probably think it would have been quicker to do it manually but trust me, spending some time getting this right will help you no end on understanding how to make complex formulas work – whether in a spreadsheet, in building complex conditional format rules etc etc.
If you are completely stuck then the answer is at the end of this blog. Good luck!