Excel Practice
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

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.

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

Every session is the same length, given in B6. Work out the beginners' total hours in D2.

To begin, type it exactly:

=C2*$B$6

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

    Every session is the same length, given in B6. Work out the beginners' total hours in D2. To begin, type it exactly: =C2*$B$6.

    Hint. B6 needs locking.

  2. 2

    Now the cost of the beginners' class in E2: their hours at the rate in B7.

    Hint. Two cells, one of them locked.

  3. 3

    E3 was filled down from E2 and has come out at zero. Repair it.

    Hint. Which of the two should not have moved?

  4. 4

    In E6, use SUM to total what all three classes cost.

    Hint. Column E, three rows.

  5. 5

    The session length in B6 is about to change from 1.5 to 2. Before it does, read =C3*$B$6 and say what the improvers' hours are now.

    Hint. Sessions times session length.

  6. 6

    One last reading. Somebody wrote the cost formula as =D4*B$7. Say what it returns in E4.

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