Absolute Addresses: No More Magic
Contents
    Absolute Addresses: No More Magic

Section:
    Absolute Addresses: No More Magic

Relative addressing with copy and paste is a wonderful tool. But there are times -- not many times, granted, but times nonetheless -- that we don't want the formulas to change.

A B C
1 Salespeople And Sales
2
3 Commrate 8%
4
5 Person Gross Sales Commission
6 Bill $135,000 =B6*C3
7 Mike $110,000
8 Molly $225,000

So far, so good. We can see that Bill's commission is computed by multiplying the amount of sales (in cell B6) times the commission rate (up in cell C3). We've got a formula, it seems to work: let's copy the tar out of it

A B C
1 Salespeople And Sales
2
3 Commrate 8%
4
5 Person Gross Sales Commission
6 Bill $135,000 =B6*C3
7 Mike $110,000 =B7*C4
8 Molly $225,000 =B8*C5

Well...not too good. The rows changed for the person (B6,B7,B8), but look at the commission rate reference! It changed too, and the bottom two formulas don't do what we need them to do.

This is a case of the computer not being able to magically tell when we want the reference to change and when we want it to stay the same. We just need a way to tell it.

Subsection(s)

  • Absolute cell addressing

    • Absolute cell addressing

    The way to "turn off the magic" is to use absolute cell addressing. By putting a dollar sign before the letter and number in an address (see the examples), you can tell the machine, "Hey! When I say A4, I mean A4!".

    By changing our original sheet to use the absolute address $C$3
    A B C
    1 Salespeople And Sales
    2
    3 Commrate 8%
    4
    5 Person Gross Sales Commission
    6 Bill $135,000 =B6*$C$3
    7 Mike $110,000
    8 Molly $225,000

    We can copy the formula and see that the absolute address doesn't change, while the relative address does.

    A B C
    1 Salespeople And Sales
    2
    3 Commrate 8%
    4
    5 Person Gross Sales Commission
    6 Bill $135,000 =B6*$C$3
    7 Mike $110,000 =B7*$C$3
    8 Molly $225,000 =B8*$C$3

    Q. 10
    So, what good are absolute addresses, really?

    Click here for the answer:

    Q. 11
    Why use a cell reference at all? Why not just have the formula be =B6*8%?

    Click here for the answer:

    Q. 12
    In the spreadsheet above, which of the following formulas would act just like "=B6*$C$3"? ...and why?

    Click here for the answer:

    Bill Dueber