- Practice
- About 17 min
- 5 practice questions
Relative, absolute and mixed references: copy-and-predict tasks
Predict exactly how a formula changes when copied, choose between A1, $A$1, $A1 and A$1, and build a multiplication table and a percentage-of-total column with one formula each.
If something looks wrong, please tell us.
A spreadsheet is fast because one formula, filled across a table, does hundreds of calculations. That only works if each reference behaves correctly when copied. Questions on this topic usually give a formula, a copy destination and ask for the new formula or its value. Calc and Excel follow the same rules.
The rule for copying
Count how far the copy moves: so many rows down (or up) and so many columns right (or left). Every relative part of every reference moves by that amount. A part with a $ in front of it does not move. Then evaluate.
| Reference | Column when copied across | Row when copied down |
|---|---|---|
| A1 (relative) | changes | changes |
| $A$1 (absolute) | fixed | fixed |
| $A1 (mixed) | fixed | changes |
| A$1 (mixed) | changes | fixed |
Press F4 in Excel, or Shift+F4 in Calc, while the cursor is in a reference to cycle A1 → $A$1 → A$1 → $A1 and back.
Task 1: a multiplication table with one formula
- Type 1 to 5 in A2:A6 and 1 to 5 in B1:F1.
- In B2 type =$A2*B$1. The column of A is fixed, so every copy reads the number in column A of its own row; the row of B$1 is fixed, so every copy reads the number in row 1 of its own column.
- Fill B2 right to F2, then fill B2:F2 down to row 6.
- Check one cell: D4 holds =$A4*D$1 = 3 × 3 = 9. F6 holds =$A6*F$1 = 5 × 5 = 25.
Try it with =A2*B1 instead: copied to C3 it becomes =B3*C2, which multiplies the wrong cells. Mixed references are the only way to make one formula work both across and down.
Task 2: each branch's share of the total
| Item | A | B | C |
|---|---|---|---|
| 1 | Branch | Sales | Share |
| 2 | North | 400 | =B2/$B$6 → 40% |
| 3 | South | 250 | =B3/$B$6 → 25% |
| 4 | East | 150 | =B4/$B$6 → 15% |
| 5 | West | 200 | =B5/$B$6 → 20% |
| 6 | Total | =SUM(B2:B5) → 1000 |
C2 holds =B2/$B$6 and is filled down. B2 moves to B3, B4, B5 while $B$6 stays on the total. Check: 400 ÷ 1000 = 0.4, shown as 40% in percentage format; the four shares add to 100%. Without the dollar signs, C3 would hold =B3/B7, and B7 is empty, so it would show #DIV/0!.
Copying, moving and inserting
- Copy and paste (or the fill handle): relative references adjust.
- Cut and paste (moving a formula): the references do not change; the formula still points at the same cells.
- Inserting a row or column: formulas pointing below or to the right are updated automatically, so =SUM(B2:B5) becomes =SUM(B2:B6) if a row is inserted inside the range.
- Deleting a cell that a formula uses: the formula shows #REF!.
New formula = old formula with every un-dollared row moved by the rows copied and every un-dollared column moved by the columns copied.
Key takeaways: measure the move, shift only the parts without $, then evaluate. Use $A$1 for one shared cell such as a rate or a total, $A1 or A$1 for tables that grow both ways, and remember that moving (cut) never changes references while copying does.
Check your understanding
-
1. B2 holds =$A2*B$1. It is filled to D4. What formula is in D4?
- =$A2*D$1
- =$C4*D$3
- =$A4*B$1
- =$A4*D$1
Show answer
Answer: D. The move is 2 columns right and 2 rows down. $A2 keeps column A and its row moves to 4: $A4. B$1 keeps row 1 and its column moves from B to D: D$1.
-
2. In the multiplication table (1 to 5 in A2:A6 and in B1:F1, =$A2*B$1 filled through B2:F6), what value does F5 show?
- 20
- 25
- 16
- 30
Show answer
Answer: A. F5 holds =$A5*F$1. A5 holds 4 and F1 holds 5, so 4 × 5 = 20.
-
3. C2 holds =B2/$B$6, where B2:B5 hold 400, 250, 150 and 200 and B6 holds their total. C2 is filled down to C4. What does C4 show in percentage format?
- 25%
- 40%
- 15%
- #DIV/0!
Show answer
Answer: C. C4 holds =B4/$B$6. B4 is 150 and B6 is 400 + 250 + 150 + 200 = 1000. 150 ÷ 1000 = 0.15, shown as 15%.
-
4. C1 holds =A1+B1. You CUT C1 and paste it into E5. What formula is in E5?
- =C5+D5
- =A5+B5
- =E1+F1
- =A1+B1
Show answer
Answer: D. Moving a formula with cut and paste does not change its references; it still adds A1 and B1. Copying instead would have given =C5+D5.
-
5. Which reference keeps the column fixed but lets the row change when the formula is filled down?
- A$1
- A1
- $A1
- $A$1
Show answer
Answer: C. The $ fixes whatever comes right after it. In $A1 the column letter is fixed and the row number is free to change. A$1 does the opposite.
Sign in to keep track of the lessons you finish.