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

By Ali, Founder, Excel Lessons

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.

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

In E2, type:

=SUM(B2:D2)

That totals Sourdough's three days.

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

    In E2, type: =SUM(B2:D2). That totals Sourdough's three days.

    Hint. B2 to D2.

  2. 2

    Filling copies E2 down. Each row reference shifts to match. Select E2:E4 and press CtrlD to fill into E4.

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

  3. 3

    In B5, total B2:B4 with SUM.

    Hint. B2 to B4.

  4. 4

    Filling right works the same way, across columns. Select B5:D5 and press CtrlR to fill into D5.

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

  5. 5

    E5 can total the row sums or the column totals. Read =SUM(E2:E4) and say what it returns.

    Hint. The three row totals.