Excel Practice
Lessons Lesson 10

Filling Down and Filling Right

About this lesson

Filling copies a formula into the cells next to it. Every relative reference moves by the same distance. So one formula can total 200 rows.

By the end you can

  • fill a formula down and across and predict what each copy holds.
  • tell which fills need a locked reference before you fill.

Practises SUM

The idea

Filling down adds to the row numbers. Filling right moves the column letters along. That is the whole mechanism. It is why a formula written for the first row of a table is usually the only one you need to write.

The habit to build is not the shortcut. It is the check. Total a table by rows and by columns, and compare the two grand totals. They must agree. When they do not, it is almost always a range that stopped one row short. That is the most common error in a spreadsheet, and an invisible one. A total that is slightly too small looks like a total. Two formulas and five seconds.

The mistake to watch for

A filled total that stops one row short. It is the most common error on a spreadsheet and the hardest to see. A total that is slightly too small looks like a total. Total a table by rows and by columns and compare the two grand totals. They must agree. When they do not, one range ends early.

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

Total the sourdough waste across the three days, in E2.

To begin, type it exactly:

=SUM(B2:D2)

E2
Row A B C D E F G
1 Item Mon Tue Wed Week
2 Sourdough 4 2 6
3 Baguette 9 5 3
4 Croissant 12 7 11
5 Daily total
6
7
8

Every step

  1. 1

    Total the sourdough waste across the three days, in E2. To begin, type it exactly: =SUM(B2:D2).

    Hint. B2 to D2.

  2. 2

    Copy that down from E2 to E4 and check what lands in E4. On a keyboard, select the three cells and press CtrlD.

    Hint. Whatever landed in E4 is what this step wants.

  3. 3

    Now the other direction. Total Monday's waste across all three items, in B5.

    Hint. B2 to B4.

  4. 4

    Copy B5 across to D5 and check what lands in D5. On a keyboard, select the three cells and press CtrlR.

    Hint. Whatever landed in D5 is what this step wants.

  5. 5

    The grand total in E5 could be read two ways. Read =SUM(E2:E4) and say what it returns.

    Hint. The three row totals.