Lessons Lesson 6

Fill Down and Fill Right

About this lesson

Ctrl+D copies the top cell of a selection into the cells below it. Ctrl+R copies the left cell across. Every reference moves with the copy.

By the end you can

  • write one formula and fill a whole column or row with it.
  • say which references move when you copy a formula.
  • spot a filled formula that points at an empty cell and shows 0.

Practises SUM

By Ali, Founder, Excel Lessons

The idea

The moving reference is the whole feature. Copy a row total from row 2 down to row 4, and every reference in it moves down two rows. A normal reference travels with its formula. That is exactly what a column of row totals needs.

It is not what you want when the formula points at one fixed cell. A tax rate, an exchange rate, a target in the corner of the sheet. Fill that formula down and every copy points one row lower, at an empty cell. There is no error. The fix is a dollar sign, and it is the first lesson of the Cell References module. Almost every broken filled column has a missing dollar sign.

The mistake to watch for

Filling a formula that points at one fixed cell. The reference to the rate moves down with every copy and lands on an empty cell. The results are clearly wrong, but there is no error. Before you fill, look at every reference and ask: should this move? The ones that should not move need dollar signs. The next module teaches them.

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

A range can run across a row, not only down a column.

In E2, type:

=SUM(B2:D2)

B2:D2 is the three months on the desk lamp row.

E2
Row A B C D E F G
1 Product Jan Feb Mar Total
2 Desk lamp 42 51 38
3 Wall clock 27 19 33
4 Storage box 64 58 71
5 Monthly total
6
7 Commission rate 0.15
8

Every step

  1. 1

    A range can run across a row, not only down a column. In E2, type: =SUM(B2:D2). B2:D2 is the three months on the desk lamp row.

    Hint. Equals, SUM, B2:D2 in brackets.

  2. 2

    CtrlD copies the top cell of a selection down, and each copy moves one row. E3, filled down from E2, reads =SUM(B3:D3). Say what E3 shows.

    Hint. Row 3's three months.

  3. 3

    CtrlD fills the top formula down the selection. Select E2:E4 and press CtrlD. E4 now holds the storage box total.

    Hint. Select E2 to E4, then CtrlD.

  4. 4

    CtrlR fills the left formula across the selection. B5 already totals January. Select B5:D5 and press CtrlR. D5 now holds the March total.

    Hint. Select B5 to D5, then CtrlR.

  5. 5

    F2 works out commission: the total times the rate in B7. Filled down, F3 reads =E3*B8. Say what F3 shows.

    Hint. Look at what is in B8.