Excel Practice
Lessons Lesson 8

Absolute References and the Dollar Sign

About this lesson

A dollar sign in front of part of a reference stops that part from changing when you copy the formula. So $B$7 always points at B7.

By the end you can

  • lock a cell with $B$7 so it does not move when the formula is filled.
  • decide, reference by reference, whether it should move.
  • fix a filled column whose rate reference slid.

Practises SUM

The idea

Ask one question before you type a dollar sign: when I copy this formula, should this reference move? A rate, a tax percentage, a conversion factor, anything written once and used by every row, should not move. The cells that belong to the row should. Get that right and you will never think about it again.

This matters because of what happens when you get it wrong. A reference that should have been locked does not produce an error when it moves. It lands on an empty cell. An empty cell in a multiplication is 0. The column fills with numbers that are too small, and it looks completely normal. Excel will not tell you. The only warning sign is a total that changed when you copied something. By then the formula looks fine, because you are reading the copy.

The mistake to watch for

A rate without dollar signs does not fail when you fill the formula. The reference moves onto the empty cell below the rate. A multiplication by an empty cell is 0. So the column fills with numbers that are too small, and it looks normal. Check the last row of anything you fill. If the rate cell it names is empty, the lock is missing.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Work out the hire cost of the Luton box: days multiplied by its daily rate.

To begin, type it exactly:

=B2*C2

D2
Row A B C D E F G
1 Vehicle Days Daily rate Hire cost With insurance
2 Luton box 3 84
3 Transit 5 62
4 Crew cab 2 71
5 Tipper 4 95
6
7 Insurance per day 18
8

Every step

  1. 1

    Work out the hire cost of the Luton box: days multiplied by its daily rate. To begin, type it exactly: =B2*C2.

    Hint. The two numbers on row 2.

  2. 2

    Insurance costs the same per day for every vehicle. The rate is in B7. In E2, add the Luton box's insurance to its hire cost. Lock the rate so it cannot move.

    Hint. The rate cell needs two dollar signs.

  3. 3

    E3 was copied down from E2 and the insurance has gone wrong. Look at what the copy did to the reference, and repair it.

    Hint. The two references that should move already did. One should not have.

  4. 4

    In E5, work out the total for the tipper: hire cost plus insurance. Use SUM somewhere in it. The insurance rate must stay locked.

    Hint. Whatever shape you use, B7 needs both dollar signs.

  5. 5

    Somebody has locked the wrong thing. Read =D4+B4*$B7 and say what it returns in E4.

    Hint. Only the column is locked. The row is not.