Absolute references ($) practice exercises
When you copy a formula down, every cell reference moves with it. That's usually what you want, except for a setting like a commission rate that every row must use. A $ locks the reference: $K$1 stays put when the formula is copied.
Syntax
A1 (moves) · $A$1 (locked) · A$1 (row locked) · $A1 (column locked)
A1- Relative: changes to A2, A3... as you copy down.
$A$1- Absolute: always A1. Use it for settings, rates and totals.
A$1 / $A1- Mixed: only the row or only the column is locked.
The practice data
The orders table again, plus three settings in column K that formulas must keep pointing at. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales | Commission | 0.05 | |||
| 2 | Ana | East | Widget | 12 | 5 | 60 | Unit target | 8 | |||
| 3 | Ben | West | Gadget | 5 | 20 | 100 | Markup | 0.2 | |||
| 4 | Cara | East | Gadget | 8 | 20 | 160 | |||||
| 5 | Dev | North | Widget | 20 | 5 | 100 | |||||
| 6 | Ana | West | Gizmo | 3 | 50 | 150 | |||||
| 7 | Ben | East | Widget | 7 | 5 | 35 | |||||
| 8 | Cara | North | Gizmo | 2 | 50 | 100 | |||||
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=F2*$K$1
Commission for each row: the Sales in that row times the one rate in K1. Copied down to row 9, the F reference moves but $K$1 stays. Result:
| 3 |
| 5 |
| 8 |
| 5 |
| 7.5 |
| 1.75 |
| 5 |
| 6 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return each row's share of total Sales (Sales divided by the sum of all Sales). Copied down to H9.
2 Return how far each row's Units are above or below the target in K2. Copied down to H9.
3 Return each row's Price including the markup in K3 (Price × (1 + markup)). Copied down to H9.
Practice Absolute references ($) in the full lesson, free
Lesson 3 has more exercises in a real spreadsheet grid, with hints and progress tracking. No card needed.
Open lesson 3Common Absolute references ($) mistakes
- Forgetting the $ on a setting cell: the first row is right, and every copied row points at an empty cell below it.
- Locking everything with $, so copied formulas all repeat the first row's answer.
- Locking only the column (
$K1) when copying down, where it's the row that must be locked.
FAQ
Is there a shortcut for adding $?
Yes. Put the cursor on a reference and press F4 (Windows) or Fn+F4 / ⌘+T (Mac) to cycle through $A$1, A$1, $A1 and A1.
When do I need a mixed reference?
When a formula is copied both down and across, such as a multiplication table: =$A2*B$1.