Transcripts for the above video clip:

REFERENCING

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.