Unit 3 of 12 Free

Cell references

Fill down without breaking anything.

Key ideas

=A2*B2 -> =A3*B3

Fill a formula down one row and every plain reference shifts down one row too.

=B2*$F$1

A dollar sign keeps that column or row fixed. $F$1 always points at F1.

=$A2*B$1

$A2 locks the column, B$1 locks the row. Perfect for grids like a times table.

C2 contains =A2*B2. You fill it down to C3. What does C3 contain?
- =A3*B3
- =A2*B2
- =A3*B2
- =B3*C3
Show answer
=A3*B3. Relative references shift by the same distance you moved.
C2 contains =B2+1. You copy it one cell right to D2. What does D2 contain?
- =C2+1
- =B2+1
- =B3+1
- =D2+1
Show answer
=C2+1. Moving right shifts column letters right by one.
Write revenue (Units times Price) in E2 so it still works when filled down to E7.
A B C D E
1 Product Region Units Price Revenue
2 Notebook East 120 3.5
3 Pen West 340 1.25
4 Stapler East 45 12
5 Binder North 80 6.75
6 Marker West 210 2.4
7 Folder North 150 1.9
Hint: Plain references like C2 and D2 shift correctly when filled down.
Show answer
=C2*D2 in E2. Write one formula, fill it down, and Excel adjusts every row.
C2 has =A2+B2. Build what C5 will contain after filling down.
Show answer
=A5+B5. Three rows down means every row number goes up by 3.
Why are relative references the default?
- Most formulas repeat the same pattern on every row
- They calculate faster
- They cannot break
- Excel cannot lock references
Show answer
Most formulas repeat the same pattern on every row. Row-by-row patterns are the most common thing people build.

What does the $ in $F$1 do?
- Locks the reference so it does not move when copied
- Formats the cell as currency
- Makes the formula faster
- Marks the cell as an input
Show answer
Locks the reference so it does not move when copied. $ is about locking, not money.
The tax rate lives in F1. Write tax for the Notebook in E2 so it works when filled down to E7.
A B C D E F
1 Product Region Units Price Tax 0.08
2 Notebook East 120 3.5
3 Pen West 340 1.25
4 Stapler East 45 12
5 Binder North 80 6.75
6 Marker West 210 2.4
7 Folder North 150 1.9
Hint: If F1 is not locked, it becomes F2, F3 and so on when filled down. Use $F$1.
Show answer
=D2*$F$1 in E2. Lock any single input cell that every row should share.
Which reference stays exactly the same no matter where you copy it?
- $A$1
- A1
- A$1
- $A1
Show answer
$A$1. Dollar signs on both the column and the row lock both.
Build tax using the locked rate in F1
Show answer
=D2*$F$1. Price moves with each row, the rate stays put.
B8 holds total units. Show each product's share of the total in E2, working when filled to E7.
A B C D E
1 Product Region Units Price Share
2 Notebook East 120 3.5
3 Pen West 340 1.25
4 Stapler East 45 12
5 Binder North 80 6.75
6 Marker West 210 2.4
7 Folder North 150 1.9
8 Total 945
Hint: Divide this row's units by the locked total $C$8.
Show answer
=C2/$C$8 in E2. Percent of total is one of the most common absolute reference patterns.

What does A$1 lock?
- Only the row
- Only the column
- Both
- Nothing
Show answer
Only the row. The $ sits in front of what it locks. Here it is in front of the row number.
Match each reference to its behavior
A1
Moves both ways
$A$1
Never moves
$A1
Column locked
A$1
Row locked
Build a times table. Write one formula in B2 that works for the whole grid B2:F6.
A B C D E F
1 1 2 3 4 5
2 1
3 2
4 3
5 4
6 5
Hint: Lock column A for the left numbers and row 1 for the top numbers: =$A2*B$1.
Show answer
=$A2*B$1 in B2. One formula filled across and down. That is the power of mixed references.
B2 has =$A2*B$1. You copy it to D4. What does D4 contain?
- =$A4*D$1
- =$A2*D$1
- =$A4*B$1
- =A4*D1
Show answer
=$A4*D$1. Column A stays locked, row 1 stays locked, everything else moves.
Build a reference with the column locked and the row free
Show answer
=$A2. $ before the letter locks the column only.