Tip:
Highlight text to annotate it
X
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.