Excel Practice
Lessons Lesson 7

Relative References

About this lesson

A relative reference describes where a cell is compared to the formula. Copy the formula somewhere else, and the reference moves by the same amount.

By the end you can

  • say what a copied formula will point at before you copy it.
  • fill a column of row calculations from one formula.
  • spot a range that moved past the data and shows 0.

Practises SUM

The idea

Every reference you type is relative unless you say otherwise. A formula in D2 that multiplies the two cells to its left does not really mean B2 and C2. It means "the cell two to the left" and "the cell one to the left". Copy it into D3 and it still means that, so it points at B3 and C3. This is why you can write one formula once and fill it down 200 rows.

The mistake it causes is always the same. It is not filling down; that works. It is filling sideways by accident. Usually you drag the small square at the corner of the selection one cell too far. A range that moves past the data does not fail. SUM over four empty cells is 0. A column of zeros looks like real answers until somebody checks one. If a total suddenly shows 0, look at which cells its range points at first.

The mistake to watch for

Filling down is safe. The damage comes from filling sideways by accident, usually by dragging the corner of a selection one cell too far. The range moves past the data. SUM over empty cells is 0, not an error, so the column looks finished. When a total suddenly shows 0, look at which cells its range names before you look at anything else.

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 what the sweet peas came to: packets multiplied by the price each.

To begin, type it exactly:

=B2*C2

D2
Row A B C D E F G
1 Seed Packets Price each Line total
2 Sweet pea 12 2.4
3 Cosmos 8 1.95
4 Nasturtium 20 1.6
5 Foxglove 5 3.25
6 Total
7
8

Every step

  1. 1

    Work out what the sweet peas came to: packets multiplied by the price each. To begin, type it exactly: =B2*C2.

    Hint. The two numbers on the sweet pea row.

  2. 2

    That formula was copied down into D3. Excel adjusted it as it went, so D3 now holds =B3*C3. What does D3 show?

    Hint. The formula names row 3, so read row 3.

  3. 3

    Do the same for the nasturtiums in D4.

    Hint. Row 4 this time.

  4. 4

    Add up the four line totals in D6.

    Hint. Four cells, top to bottom.

  5. 5

    Here is the catch. That total was copied one column to the right, into E6, where it reads =SUM(E2:E5). What does it show?

    Hint. Look at what is in column E.