Formulaic Confusion Part 3: Absolute References

OK, so the final part of our 3-day series on formulas is about absolute references. A cell reference usually looks like this:

D4

Perfectly normal and straightforward so far, so we're going to incorporate this reference into a formula:

=B6*D4+B6

So we're going to multiply the contents of B2 by A3. Still with me? Good. We're going to make lots of copies of this formula though. Imagine that your worksheet looks like this:












You may notice that the formula example is the same from the BEDMAS entry we made (I'll give you a clue - if you haven't read it already, you should!). The formula is going to be located in cell C6, and we are going to copy the formula down from C6:C15. The problem with this, though, is the way Excel copies formulas.

When you copy down one row, it changes the formula you just made by moving any rows referenced in there by one. So if we copy the contents of C6 (=B6*D4+B6) down to C7, the formula will change to (=B7*D5+B7). This is how Excel always works - it usually makes it easier to control your formulas, but here our reference to D4 has changed to D5 which is an empty cell.

To control this, we need to make the reference to D4 absolute instead of relative. Do you see the connection with the words there? Absolute means any reference (to columns or rows) will "stick" when you copy them. Relative means the references will change according to how far you've copied the formula.

So we start adding symbols to indicate the reference(s) will be absolute. The symbol we use is the dollar sign ($). There are three ways of displaying an absolute reference to a cell:

$A$1 $A1 A$1

The first reference ($A$1) means that both the column and row will stay the same when this reference is copied.
The second reference ($A1) means that only the column will stay the same when this reference is copied.
The third reference (A$1) means that only the row will stay the same when this reference is copied.
There's an easy way to make any reference absolute without having to always type in the $. In the formula bar, click once in any part of the reference to the cell you want to make absolute. Then press the [F4] key in the very top row of your keyboard. If you press it multiple times, it will cycle through the different ways of making cell references absolute that I just mentioned above.

So with our knowledge of absolutes, lets quickly return to the example I gave. We're going to make our reference to D4 absolute. So it will look like this:

=B6*D$4+B6

You could also say:

=B6*$D$4+B6

As long as you are comfortable that the cell referenced is absolute the way you want it to be, there's no "wrong" way of doing this.

So thats the end of the three part series on formulas. With thanks to the Experience Matters student who started all this, who made it VERY clear that she preferred to remain anonymous! If you have any further questions, be sure to leave a comment, or email at officegirlgreen @ gmail.com (remove the spaces to email me!).