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

By Ali, Founder, Excel Lessons

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.

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

Line total is packets times price each.

In D2, type:

=B2*C2

That is the sweet pea row.

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

    Line total is packets times price each. In D2, type: =B2*C2. That is the sweet pea row.

    Hint. The two numbers on the sweet pea row.

  2. 2

    D2 was copied down into D3. Excel adjusted it as it went, so D3 now holds =B3*C3. Say what D3 shows.

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

  3. 3

    In D4, multiply packets by price for row 4. Shape: =packets*price.

    Hint. Row 4 this time.

  4. 4

    In D6, total D2:D5 with SUM.

    Hint. Four cells, top to bottom.

  5. 5

    D6 was copied one column right, into E6, where it reads =SUM(E2:E5). Say what E6 shows.

    Hint. Look at what is in column E.