• 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.

ReferenceColumn when copied acrossRow when copied down
A1 (relative)changeschanges
$A$1 (absolute)fixedfixed
$A1 (mixed)fixedchanges
A$1 (mixed)changesfixed

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

  1. Type 1 to 5 in A2:A6 and 1 to 5 in B1:F1.
  2. 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.
  3. Fill B2 right to F2, then fill B2:F2 down to row 6.
  4. 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

ItemABC
1BranchSalesShare
2North400=B2/$B$6 → 40%
3South250=B3/$B$6 → 25%
4East150=B4/$B$6 → 15%
5West200=B5/$B$6 → 20%
6Total=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. 1. B2 holds =$A2*B$1. It is filled to D4. What formula is in D4?

    1. =$A2*D$1
    2. =$C4*D$3
    3. =$A4*B$1
    4. =$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. 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?

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

    Answer: A. F5 holds =$A5*F$1. A5 holds 4 and F1 holds 5, so 4 × 5 = 20.

  3. 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?

    1. 25%
    2. 40%
    3. 15%
    4. #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. 4. C1 holds =A1+B1. You CUT C1 and paste it into E5. What formula is in E5?

    1. =C5+D5
    2. =A5+B5
    3. =E1+F1
    4. =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. 5. Which reference keeps the column fixed but lets the row change when the formula is filled down?

    1. A$1
    2. A1
    3. $A1
    4. $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.

Privacy · Terms · Refunds · Delivery