- 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!.
| Reference | Copied 2 columns right and 3 rows down |
|---|---|
| B2 | D5 |
| $B2 | $B5 |
| B$2 | D$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
| Cells selected | Next three cells |
|---|---|
| 6 and 9 | 12, 15, 18 |
| 40 and 35 | 30, 25, 20 |
| 100 and 200 | 300, 400, 500 |
| Jan | Feb, 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. F2 holds =C2*D2. After copying down to F4, F4 holds:
- =C2*D4
- =C4*D2
- =C4*D4
- =C3*D3
Show answer
Answer: C. Both references are relative, so both rows shift by 2: =C4*D4.
-
2. B2 holds =A2*$D$1. After copying to B6, B6 holds:
- =A6*$D$1
- =A6*$D$5
- =A2*$D$1
- =A6*D1
Show answer
Answer: A. A2 moves down 4 rows to A6, while $D$1 is absolute and stays.
-
3. B2 holds =$A2+B$1 and is copied to D4. D4 holds:
- =$A2+D$1
- =$A4+D$1
- =$A4+B$1
- =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. B1 holds =A1 and is copied one cell to the left, into A1. What does that cell show?
- #REF!
- 0
- #DIV/0!
- 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. Two cells hold 40 and 35. They are selected and the fill handle is dragged to make five cells. The fifth cell holds:
- 25
- 15
- 30
- 20
Show answer
Answer: D. The step is -5, so the cells read 40, 35, 30, 25, 20.
Sign in to keep track of the lessons you finish.