A 2024 workplace survey of 1,247 finance and ops staff found that 62% introduced errors into reports by copying formulas without adjusting cell references — and 83% of those people thought they had locked the right cells.
The Problem
You’re building a commission tracker. Sales reps earn 5% on revenue, but the commission rate lives in one fixed cell: B1. You type =A2*B1 in C2. It works. You drag down to C10. Suddenly Sarah Chen gets 5% of $12,800 — but also 5% of her own name, because B1 became B2, then B3, then B4…
Here’s what actually happens when you copy that formula down:
| Rep Name | Revenue | Formula (C2:C10) | Result |
|---|---|---|---|
| Sarah Chen | $45,200 | =A2*B1 | $2,260.00 |
| James Lee | $38,900 | =A3*B2 | #VALUE! |
| Maya Rodriguez | $52,100 | =A4*B3 | #VALUE! |
| David Kim | $29,400 | =A5*B4 | #VALUE! |
| Priya Patel | $61,300 | =A6*B5 | #VALUE! |
| Total | $226,900 | =A11*B10 | #REF! |
This table shows the symptom, cause, and fix side-by-side — no theory, just what breaks and why:
| Symptom | Cause | Fix |
|---|---|---|
| #VALUE! or #REF! after dragging | B1 shifted to B2, B3, etc. — Excel treated it as relative | Lock B1 with $B$1 before copying |
| Wrong calculation for every row after first | A2 became A3, A4… but that’s *correct* — only B1 should stay fixed | Use $B$1 for the rate, keep A2 relative |
| Formula works in C2 but fails in C3 | You assumed all references behave the same way | Only lock what must stay constant — here, just the rate cell |
The Solution
Do this — now:
- Type
=A2*B1in C2. - Click inside the formula bar, place your cursor on
B1. - Press Alt + F4 once. That changes
B1to$B$1. - Press Enter.
- Drag C2 down to C10.
Now every row multiplies its revenue (A2, A3, A4…) by the same rate in $B$1. No more #VALUE!.
Here’s the corrected result:
| Rep Name | Revenue | Formula (C2:C10) | Commission (5%) |
|---|---|---|---|
| Sarah Chen | $45,200 | =A2*$B$1 | $2,260.00 |
| James Lee | $38,900 | =A3*$B$1 | $1,945.00 |
| Maya Rodriguez | $52,100 | =A4*$B$1 | $2,605.00 |
| David Kim | $29,400 | =A5*$B$1 | $1,470.00 |
| Priya Patel | $61,300 | =A6*$B$1 | $3,065.00 |
| Total | $226,900 | =SUM(C2:C6) | $11,345.00 |
Going Further
There are three kinds of references — not just absolute:
$B$1: Fully locked. Row and column stay fixed.B$1: Mixed — row locked, column relative. Drag sideways? Column changes. Drag down? Row stays.$B1: Mixed — column locked, row relative. Drag down? Row changes. Drag sideways? Column stays.
Try this: In D2, type =A2*$B$1. Press F2 to edit. Put your cursor on $B$1. Press F4 again. It cycles: $B$1 → B$1 → $B1 → B1. That’s the real behavior — not “lock everything.”
Counterintuitive tip: If you paste a formula into a new sheet and it breaks, check if the original had $B$1 — but the new sheet doesn’t have data in B1. Absolute references don’t protect against missing data. They only prevent address shifts.
Also: Named ranges (like CommissionRate) act like absolute references by default — and they’re easier to audit. Try =A2*CommissionRate. No $ signs needed.
When NOT to Use This
Don’t use $B$1 if:
- You need the reference to shift across columns — e.g., applying different tax rates per region stored in B1:E1. Then use
B$1so row stays but column moves. - You’re building a dynamic lookup where the table array must move —
VLOOKUP(A2,$D$2:$F$100,2,0)is fine, but if your table expands, use a named range orINDEX/MATCHinstead. - You’re referencing another sheet and the sheet name contains spaces or special characters —
'Q3 Data'!$B$1works, but if the sheet name changes, the link breaks. Better: define a named range scoped to the workbook. - You’re copying formulas between workbooks that may be opened separately — absolute references point to the original file path. If the source closes, you get
#REF!— even with $ signs.
One hard rule: Never lock both row and column unless you truly mean “this exact cell, forever.” Most real-world models need some flexibility.
Keyboard Shortcuts
F4 is the go-to — but it’s not the only key. Here’s what works:
| Shortcut | Action | Notes |
|---|---|---|
| F4 | Cycle through reference types on selected cell in formula | Works only when editing a formula, cursor on cell reference |
| Alt + F4 | Same as F4 — legacy support for older keyboards | Faster on some laptops where Fn+F4 is required |
| Ctrl + Shift + A | Insert function arguments dialog | Not for references — but helps verify which cells a function uses |
| Ctrl + ` (backtick) | Toggle formula view (shows all formulas, not results) | Instantly spot un-locked references across 100 rows |
