Different types of References

Ever inherited a Microsoft Excel spreadsheet from someone, looked at the formulas and thought “what are all these dollar signs about?”.

Well, they are Excel’s way of defining what will happen to Cell References if you ever copy that formula to a different location.

In fact, there are three types of references available – Relative ReferencesAbsolute References and Mixed References.

Relative References

These are by far the most common type of reference that you will come across.  Have a look at this illustration:

Absolute References A
This formula simply multiplies Units by Price to get the resultant Cost.  Now, of course we want to do exactly the same calculation for all the other rows in our example. As our formula contains Relative References, all we have to do is copy it down the column, and Excel takes care of the rest.
[Hint – double-clicking the Fill Handle at the bottom right corner of the original cell is by far the fastest way of doing this]

Absolute References B

As Excel copies the formula, it adjust the references to make sense at the new location.  By the time we get to Row 15, the formula says =B15*C15

Behind the scenes, Excel thinks about these formulas in in a Relative way:

“Take the cell two to the left of where I am and multiply it by the cell one to the left of where I am

Considering each of our formulas in that light, they will always give the correct answer.

Absolute References

Here is a different example:

Absolute References C2
We want to calculate the Interest due of several different Loan amounts.  It makes no sense to have the Rate in the spreadsheet against each amount, because it is the same for all of them. So we have put in cell D2.

Unfortunately, the way Excel adjusts the formulas (which was helpful in the previous example), trips us up here:

Absolute References D

 

The first part of the formula (B15) has been updated the way we wanted, but the second part has also been changed.  By the time we get to row 15, it is pointing to D12 – because that cell is empty, the result come out as zero !

Excel is still thinking in its Relative way:

“Take the cell one to the left of where I am and multiply it by the cell one to the right and three above where I am

Think that through and you will understand where the formula in cell C15 has come from.

How do we get around this – use the Dollar sign.  Alter the original formula to say =B5*$D$2 and then copy it down the column.
[Hint – the easiest way to do this is to click next to “D2” in the formula and then press F4 on the keyboard]

Absolute References E

 

The Dollar signs say to Excel – “If I ever copy this formula somewhere else, please don’t change the reference to D2” – So we can now copy the formula with ease.

The other benefit of this technique comes when we decide to up the Interest Rate to 4%.  We only have to change that figure in one place.

Mixed References

Now here is the bit to really make you think.  (Everything I’ve said so far, I teach during our Intermediate Microsoft Excel course – but this bit I don’t cover until the Advanced session).

What we did in the previous example was a bit of overkill.  Putting a Dollar sign in front of the row number (2) is definitely needed. When we copy the formula down, we don’t want that number to change to 3, 4, 5 etc.

However, we had no intention of copying the formula sideways, so the Dollar sign in front of the column letter (D) doesn’t give us any benefit – [it doesn’t do any harm either!]

Consider this example:

Absolute References F

 

This time we want to compare the results for two different Interest Rates.  Can we do that by writing just a single formula?
[would I have asked if we couldn’t !]

Let’s see what happens if we use Relative References:

Absolute References G

What happened in cell C6 is easy.  As we copied the formula down from the row above we got =B6*D3 – and because D3 is empty, the result is zero.

The answer in cell D5 is a bit more subtle.  When we copied the formula one cell to the right, Excel adjusted the column letters and gave us =C5*E2.  It is multiplying by the correct Interest Rate, but it isn’t using the Loan amount anymore.

The correct formula to use here is – =$B5*D$2
[Hint – click next to “B5”, press F4 repeatedly – (it will cycle through all the possibilities) –  do the same when clicked next to “D2”.

Absolute References H

 

Now in Cell C6 – (where we have copied the formula down):

  • We pick up the correct Loan Amount – ($B5 has adjusted to $B6)
  • We stay with Rate 1 – (D$2 doesn’t get adjusted)

In Cell D5 – (where we have copied the formula across):

  • We pick up the correct Loan Amount – ($B5 doesn’t get adjusted)
  • We move to Rate 2 – (D$2 has adjusted to E$2)

This will work for the entire block of cells:

Absolute References I

 

 To Sum up

Supposing you were writing a formula Microsoft Excel which refers to cell H2 (for example).
You can that write that reference in four different ways:

H2 – This is a Relative Reference
Copy it sideways and it will adjust to I, J, K…
Copy it down and it will adjust to 3, 4, 5…

$H2 – This is a Mixed Reference
Copy it sideways, it is fixed on column H
Copy it down and it will adjust to 3, 4, 5…

H$2 – This is a Mixed Reference
Copy it sideways and it will adjust to I, J, K…
Copy it down, it is fixed on row 2

$H$2 – This is an Absolute Reference
Copy it sideways, it is fixed on column H
Copy it down, it is fixed on row 2
Wherever you copy this formula to, it will still point at cell H2

Phew, now you know as much about Cell References as I do.  Time for a lie down !