Goal
You’ll be able to use absolute cell references correctly with the dollar-sign lock, and understand precisely why AutoFill’s usually-helpful automatic adjustment becomes a genuine problem in a specific, common situation.
Learn
From the previous lesson, you know AutoFill automatically shifts cell references as a formula is copied — usually exactly what you want. But consider calculating each item’s percentage of a single, fixed total:
=A1/B1
If B1 holds a fixed total that every single row needs to divide by (not a different total per row), AutoFilling this down would incorrectly shift B1 to B2, B3, and so on — but you need every row to keep referencing that same fixed B1, not a different cell each time.
The fix is an absolute reference, using dollar signs to lock a reference in place so AutoFill won’t shift it:
=A1/$B$1
The $ before both the column letter and row number locks that reference completely — AutoFilling this formula down keeps $B$1 exactly fixed on every row, while A1 still correctly shifts to A2, A3, and so on, since only the total (not the individual amount) needs to stay fixed.
You can lock just the column ($B1), just the row (B$1), or both ($B$1), depending on exactly what needs to stay fixed as a formula is copied in different directions. Pressing F4 after clicking a reference cycles through these different locking combinations automatically, without needing to type the dollar signs manually each time.
Decision Task
You’re calculating each of 20 products’ percentage of a single grand total in cell B1, using =A1/B1 in the first row. Before reading on: what happens if you AutoFill this down all 20 rows without using an absolute reference?
Show Answer
Each row would incorrectly divide by a different, shifted cell (B2, B3, B4…) instead of consistently dividing by the actual fixed grand total in B1 — producing wrong, meaningless results for every row except the first. The fix is writing the formula as =A1/$B$1, locking B1 in place so it stays fixed across every row while A1 still correctly shifts per row.
Common Mistake
AutoFilling a formula that needs one reference to stay fixed, without actually locking that reference with a dollar sign first. This is a genuinely common, easy-to-miss error, since the formula still calculates something for every row — it just calculates the wrong thing, often without any obvious visual sign that something is wrong until you actually check the numbers carefully.
Practice Questions
1. Write a formula that divides A1 by a fixed value in C1, correctly locked so it won’t shift when AutoFilled down.
Show Answer
=A1/$C$1
2. What does pressing F4 after clicking a cell reference do?
Show Answer
Cycles through the different absolute/relative locking combinations for that reference (fully locked, column-only locked, row-only locked, fully relative), without needing to manually type dollar signs.
3. Write a reference that locks only the column (A), but still allows the row number to shift when AutoFilled.
Show Answer
$A1
4. True or False: an absolute reference means the cell’s actual value can never change.
Show Answer
False — an absolute reference only affects whether AutoFill shifts that reference when copying the formula; the cell’s actual value can still be edited or changed normally.
5. Why might you need to lock only the row, not the column, in a specific formula?
Show Answer
If a formula needs to always reference a value from one specific fixed row, but the column itself should still shift as the formula is copied sideways across different columns.
Try It Yourself
Without looking back, write a formula that calculates a bonus by multiplying A1 by a single fixed bonus rate in D1, correctly set up so it can be safely AutoFilled down many rows.
Show Answer
=A1*$D$1 — locking D1 completely so it stays fixed across every row, while A1 correctly shifts to match each row’s own value.
Quick Check
1. What does an absolute reference do?
Show Answer
Locks a cell reference so it doesn’t shift when a formula is copied or AutoFilled.
2. What symbol creates an absolute reference?
Show Answer
A dollar sign ($), placed before the column letter, row number, or both.
3. What does pressing F4 do after selecting a reference?
Show Answer
Cycles through the different locking combinations automatically.
4. What real problem does an unlocked reference cause when dividing by a single fixed total?
Show Answer
AutoFill shifts the reference to a different, wrong cell on each row, producing incorrect results everywhere except the first row.
5. Does $A$1 lock the row, the column, or both?
Show Answer
Both.