• Practice
  • About 13 min
  • 5 practice questions

Copy and fill drills: predicting formulas after copying

Predict the exact formula in a target cell after copying across, down or up, including mixed references that lock a row or column.

If something looks wrong, please tell us.

Copy questions have a fixed pattern: a formula in one cell is copied to another, and you must write the new formula. If you apply one rule carefully, every such question is quick. The rule: move each unlocked part of a reference by the same distance as the copy.

The rule in steps

  • Find how many columns and how many rows the copy moves. Right and down are positive; left and up are negative.
  • Look at every reference in the formula. Columns shift only if the column has no $; rows shift only if the row has no $.
  • Shift the unlocked parts by the distance found.
  • If a shift would take a column below A or a row below 1, the result is #REF!.
Four reference types after one copy
ReferenceCopied 2 columns right and 3 rows down
B2D5
$B2$B5
B$2D$2
$B$2$B$2

Worked examples

Example 1. F2 holds =C2*D2 and is copied down to F4. The move is 2 rows down, so each row number goes up by 2: F4 holds =C4*D4.

Example 2. B2 holds =A2*$D$1 and is copied to B6. The A2 moves 4 rows down to A6, and $D$1 is locked, so B6 holds =A6*$D$1. Every price is multiplied by the same cell D1.

Example 3. E5 holds =C5+D$2 and is copied to C3. The move is 2 columns left and 2 rows up. C goes to A and 5 goes to 3. In D$2 the column shifts from D to B but the row is locked, so the formula in C3 is =A3+B$2.

Example 4. B2 holds =$A2+B$1 and is copied to D4, a move of 2 columns right and 2 rows down. $A2 keeps the column but shifts the row: $A4. B$1 shifts the column but keeps the row: D$1. The new formula is =$A4+D$1. This is the pattern of a multiplication table.

Example 5. A formula in C1 is =A1+B1 and it is copied to the range C2:C5. The last cell C5 holds =A5+B5, and there are 5 formulas altogether including the original in C1. The copy creates 4 new formulas.

Example 6. B1 holds =A1 and is copied one column to the left into A1. The reference would need to go one column left of A, which does not exist, so the result is #REF!.

Shift only what has no dollar sign, by the same distance as the copy.

Sequences from the fill handle

Series produced by the fill handle
Cells selectedNext three cells
6 and 912, 15, 18
40 and 3530, 25, 20
100 and 200300, 400, 500
JanFeb, Mar, Apr

Fill handle questions work the same way. Two cells with 6 and 9 give a step of 3, so five cells read 6, 9, 12, 15, 18. Two cells with 40 and 35 give a step of -5, so the series is 40, 35, 30, 25, 20.

Key takeaways. Count the move in columns and rows, shift unlocked parts only, and watch for the edge of the sheet. A series from the fill handle needs two cells to fix the step.

Check your understanding

  1. 1. F2 holds =C2*D2. After copying down to F4, F4 holds:

    1. =C2*D4
    2. =C4*D2
    3. =C4*D4
    4. =C3*D3
    Show answer

    Answer: C. Both references are relative, so both rows shift by 2: =C4*D4.

  2. 2. B2 holds =A2*$D$1. After copying to B6, B6 holds:

    1. =A6*$D$1
    2. =A6*$D$5
    3. =A2*$D$1
    4. =A6*D1
    Show answer

    Answer: A. A2 moves down 4 rows to A6, while $D$1 is absolute and stays.

  3. 3. B2 holds =$A2+B$1 and is copied to D4. D4 holds:

    1. =$A2+D$1
    2. =$A4+D$1
    3. =$A4+B$1
    4. =A4+D1
    Show answer

    Answer: B. The locked column of $A stays but its row moves to 4; the locked row of B$1 stays but its column moves to D.

  4. 4. B1 holds =A1 and is copied one cell to the left, into A1. What does that cell show?

    1. #REF!
    2. 0
    3. #DIV/0!
    4. A1
    Show answer

    Answer: A. The reference would have to point left of column A, which does not exist, so the result is #REF!.

  5. 5. Two cells hold 40 and 35. They are selected and the fill handle is dragged to make five cells. The fifth cell holds:

    1. 25
    2. 15
    3. 30
    4. 20
    Show answer

    Answer: D. The step is -5, so the cells read 40, 35, 30, 25, 20.

Privacy · Terms · Refunds · Delivery