Lessons Lesson 12

References: Putting It Together

About this lesson

Relative references move when a formula is copied. Absolute references never move. Mixed references move in one direction only. Most real formulas use more than one kind.

By the end you can

  • choose relative, absolute or mixed for every reference in a formula.
  • build a costing sheet where changing one rate updates every figure.

Practises SUM

By Ali, Founder, Excel Lessons

The idea

Everything in this module on one sheet. The rule is the same every time: when I copy this formula, should this reference move? Row data moves. Facts that apply to the whole sheet do not. Anything that fills a block in two directions needs a mixed reference.

Here is where the effort pays. A locked rate is not just neat. It is what lets the sheet answer a question. Change the session length in one cell, and every hours figure and every cost moves with it at once. You can see the new number before you commit to it. A sheet with the rate typed into forty formulas cannot do that. It looks the same until the day somebody asks.

The mistake to watch for

A half-locked reference like B$7 can be correct where it was written and wrong where it is copied. It holds in one direction only. Before you fill, decide which way the formula will travel. A lock that is right for filling down is not enough for filling across.

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

B6 holds the session length. It stays locked, so it never moves.

In D2, type:

=C2*$B$6

That is sessions times session length.

D2
Row A B C D E F G
1 Class Learners Sessions Hours Cost
2 Beginners 14 8
3 Improvers 9 12
4 Advanced 6 12
5
6 Hours per session 1.5
7 Cost per hour 46
8

Every step

  1. 1

    B6 holds the session length. It stays locked, so it never moves. In D2, type: =C2*$B$6. That is sessions times session length.

    Hint. B6 needs locking.

  2. 2

    In E2, multiply D2 by the locked rate in B7. That is hours times cost per hour.

    Hint. Two cells, one of them locked.

  3. 3

    E3 was filled from E2, but its rate now points at B8, which is empty. Fix E3.

    Hint. Which of the two should not have moved?

  4. 4

    In E6, total E2:E4 with SUM.

    Hint. Column E, three rows.

  5. 5

    B6 is still 1.5. It changes after this step. Read =C3*$B$6 and say what it returns.

    Hint. Sessions times session length.

  6. 6

    Locking only the row still lets the column move. Read =D4*B$7 and say what it returns in E4.

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