How to use absolute and relative cell references to anchor your formula to look at the correct cells.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Absolute and Relative cell references
The ability to reference from one cell to another is one of Excel’s most powerful features, and the ability to make absolute and relative references increases the flexibility of Excel exponentially.
Although it is easy to use, it is important to understand what this absolute and relative referencing does when you copy.
So for example we’ve got three blocks here, each looking at a cell using different referencing techniques.
Over here this cell says $A10
what it does and using our Auditing Toolbar, that cell is looking at this cell here
and what you’ve told it is keep A constant and change the number depending where it is
so for example if we copied it across
what you’ll see is it continues to look at that cell
however if you go down, let me just clear the arrows first,
what you’ll see is that it pulls it down with it
so its kept the column A correct but now the numbers are changing.
Alternatively you’ve got this cell here where the reference is A$10, again what you are saying here is
the letter can change, but the 10 must stay the same hence the dollar sign,
so if you go down
you’ll see that that its looking at the same cell
However if you had to go across, the copy is being told to stay in row 10 but the columns can change.
The third method is using $A$10, now what you’ve done here is you’ve frozen the cell absolutely, it will only ever look at that cell.
This is very important to understand as a number of errors result out of this very issue.