What you will build
Complete “Use relative absolute and mixed references” using the visible Item, Qty, Unit price, Line total sample so each action and the final state can be checked against the source data.
Learning objectives
Open the practice workbook
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
Hands-on practice
Step 1: Confirm the source values are Pen | 3 | 5, Notebook | 2 | 20, and Folder | 1 | 12. In D2 enter =B2*C2 once, copy D2, then paste it into D3:D4. Verify the copied formulas adjust automatically to =B3*C3 and =B4*C4, producing 15, 40, and 12.
=B2*C2Step 2: Put the tax rate 0.1 in F2. In E2 enter =D2*(1+$F$2) once, copy E2, then paste it into E3:E4. Verify the row reference changes to D3 and D4 while $F$2 stays fixed, producing 16.5, 44, and 13.2.
=D2*(1+$F$2)Step 3: Put multipliers 2 and 3 in H1:I1. In H2 enter =$B2*H$1 once, copy H2, select H2:I4, and paste. Verify the mixed references adjust across columns and rows so H2:I4 becomes 6/9, 4/6, and 2/3.
=$B2*H$1Step 4: Inspect I4 in the formula bar. It should read =$B4*I$1: the $B column stays locked, row 4 changes with the copied row, column I changes with the copied column, and row $1 stays locked.
Step 5: Change F2 from 0.1 to 0.2 and verify E2 recalculates from 16.5 to 18. Restore F2 to 0.1 and confirm E2 returns to 16.5.
Checkpoint
| Item | Qty | Unit price | Line total | With tax | ×2 | ×3 |
|---|---|---|---|---|---|---|
| Pen | 3 | 5 | 15 | 16.5 | 6 | 9 |
| Notebook | 2 | 20 | 40 | 44 | 4 | 6 |
| Folder | 1 | 12 | 12 | 13.2 | 2 | 3 |
Common mistakes and fixes
Correction: return to the exact cell or range named in the step, recheck the source data, and repeat the specified command; do not type the expected result manually.
Independent practice
Add multiplier 4 in J1, enter =$B2*J$1 in J2 once, copy J2, then paste it into J3:J4. Verify 12, 8, and 4, then clear J1:J4 to restore the lesson state.
Key takeaways
- ✓Relative references change row/column as the formula moves.
- ✓Absolute $F$2 keeps both column F and row 2 fixed.
- ✓Mixed $B2 locks only the column; H$1 locks only the row.
- ✓Verify formulas in the formula bar, not only the displayed result.
- ✓Test F2 = 0.2, then restore F2 = 0.1 before finishing.
Check your understanding
Answer four questions about the practice sheet.
Next lesson
Continue in recommended learning order with Use fill handle and autofill.