Excel Practice
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

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.

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) to total the desk lamp's three months.

B2 to D2 is January, February and March on that 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) to total the desk lamp's three months. B2 to D2 is January, February and March on that row.

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

  2. 2

    The next two rows need the same formula, one row lower each time. Excel can copy it for you. Select E2 down to E4 and press CtrlD. Each copy changes its row. E3 would read =SUM(B3:D3). What does it show?

    Hint. Row 3's three months.

  3. 3

    Now do the copy yourself and check what lands at the bottom. Select E2 to E4 and press CtrlD. Then confirm E4 holds the storage box's total. On a phone, type the formula E4 should hold.

    Hint. Select E2 to E4, then CtrlD.

  4. 4

    The other direction works the same way. In B5, total January for all three products with SUM. Then select B5 to D5, press CtrlR, and check what is in D5.

    Hint. B5 first, then select B5 to D5 and CtrlR.

  5. 5

    One more, and it is the trap. The commission rate is in B7, and it applies to every product. F2 works out the desk lamp's commission: its total times the rate. F2 was copied down to F3, and both references moved. So F3 now reads =E3*B8. What does F3 show?

    Hint. Look at what is in B8.