Absolute and Relative cell references

How to use absolute and relative cell references to anchor your formula to look at the correct cells


NEW: Free Excel Fundamentals tutorials with downloadable exercises covering the Function Wizard, Formula Auditing, $ signs in Excel and the IF function.


 

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.