**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 References*, *Absolute 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:

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]

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:

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:

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]

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:

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:

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”.

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:

** 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 aRelative 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 aMixed 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 aMixed 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 AbsoluteReference

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 !