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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

Hire cost is days times daily rate.

In D2, type:

=B2*C2

That is the Luton box row.

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

    Hire cost is days times daily rate. In D2, type: =B2*C2. That is the Luton box row.

    Hint. The two numbers on row 2.

  2. 2

    Insurance is the same per day for every vehicle, and the rate is in B7. In E2, add D2 to B2 times B7, with B7 locked. Shape: =cell+cell*$B$7. Remember: two dollar signs lock a cell so it cannot move.

    Hint. The rate cell needs two dollar signs.

  3. 3

    E3 was filled down from E2, and the insurance has gone wrong. One reference moved when it should not have. Fix E3.

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

  4. 4

    In E5, use SUM to add D5 and B5 times B7. Shape: =SUM(cell, cell*cell). Remember: the rate in B7 must stay locked.

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

  5. 5

    Somebody locked only the column. Read =D4+B4*$B7 and say what it returns in E4.

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