Topic 1 of 15, EHL Maths & Excel final (AP)
Cell referencing: $A1, A$1, $A$1
When you drag a formula, Excel moves the cell references with it. A dollar sign stops that movement. A $ at the beginning (in front of the column letter) locks the column, so the reference does not move left or right. A $ in the middle (in front of the row number) locks the row, so the reference does not move up or down.
When the exam asks for it
- “fill down / drag the formula”, “use the same total / rate for every row”, “build a table with one formula”
Formulas
A1$A1A$1$A$1Keyboard shortcut
Click on the reference inside the formula (for example A1) and press the shortcut. Every press moves to the next type, so you never have to type the $ signs yourself.
A1→$A$1→A$1→$A1→A1Worked example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Formula in B2 | Dragged down to B3 | Dragged right to C2 | |
| 2 | =A1 | =A2 | =B1 | |
| 3 | =$A1 | =$A2 (row moves) | =$A1 (column stays) | |
| 4 | =A$1 | =A$1 (row stays) | =B$1 (column moves) | |
| 5 | =$A$1 | =$A$1 | =$A$1 | |
| 6 |
Step by step
- Service charge: 10% in B1, prices in A3:A10=A3*$B$1 keeps pointing to B1 when you drag down
- Multiplication table: rates in row 1, nights in column A=$A2*B$1 fills the whole table with one formula
- Share of total=B2/$B$5 keeps the grand total fixed
⚠ Exam traps
- Dragging down only changes row numbers, so A$1 (row locked) is enough to keep a total in row 1 fixed.
- Dragging right only changes column letters, so $A1 (column locked) is enough to keep column A fixed.
- If you are not sure, $A$1 locks both and never moves.
- #DIV/0! or wrong totals after dragging usually mean a missing $.
B2 contains =$A2*B$1. What is the formula after you drag it to C3?
Show answer
✓ =$A3*C$1. Column A stays (locked), row 1 stays (locked); the other parts move.
Book a lesson
Tell us your course, your exam date and the topics that worry you. We start with what matters most for your grade.
WhatsApp is preferred
The fastest way to book: send us a message and we usually answer the same day.
or email info@ytutor.net
Send a message
We answer by email or WhatsApp.