Why does your SUM formula return #REF! when you drag it down? Why does that discount rate keep changing from B2 to B3 to B4? Why did your colleague’s version work perfectly—but yours recalculated every single row wrong?
The answer isn’t broken data or hidden filters. It’s that one symbol you’re not typing—or typing in the wrong place. The dollar sign ($). But not just anywhere. Not just once. And definitely not only before the column.
The Setup
Let’s say you’re building a quarterly commission tracker for the APAC sales team at Alibaba Cloud Partners. You’ve got base salaries, deal values, and a fixed company-wide commission rate—stored once, in cell B1. You need to calculate each rep’s commission: Deal Value × Commission Rate.
| Rep Name | Deal Value | Commission Rate | Commission |
|---|---|---|---|
| Sarah Chen | $124,500 | 7.5% | =B2*C2 |
| James Tan | $98,200 | 7.5% | =B3*C3 |
| Amina Patel | $142,600 | 7.5% | =B4*C4 |
| Kenji Sato | $87,100 | 7.5% | =B5*C5 |
| Linh Nguyen | $115,300 | 7.5% | =B6*C6 |
| Rajiv Mehta | $133,800 | 7.5% | =B7*C7 |
| Yuki Yamada | $91,400 | 7.5% | =B8*C8 |
| Tariq Ali | $102,900 | 7.5% | =B9*C9 |
That looks fine—until you realize the commission rate is hardcoded in each row. If finance changes the rate next month, you’ll edit nine cells instead of one. Worse: what if you move that rate to B1 and update the formula to =B2*B1? Try dragging it down—and watch what happens.
The Challenge
You want to fix that commission rate reference—so it always points to B1, no matter where you copy the formula. That’s what people mean when they ask how do you fix a cell reference in excel. It’s not about locking the cell visually (like protecting the sheet). It’s about anchoring its address so Excel doesn’t auto-adjust it during copy/paste or fill-down.
The trap? Assuming $B1 or B$1 is enough. It’s not. You need both: $B$1. And here’s what most miss: pressing F4 *after* selecting the cell reference inside the formula bar—not while editing elsewhere.
Walking Through It
Start with cell D2. Right now it says =B2*C2. We’ll replace C2 with $B$1—but don’t type it manually. Let’s use the shortcut.
Click into D2. Click inside the formula bar, then click directly on C2 (so it’s highlighted blue). Press Alt + = — no, wait. That’s AutoSum. Wrong shortcut. Correct one: F4. Hit it once.
Now C2 becomes $C$2. Hit F4 again → C$2. Again → $C2. Fourth time → back to C2. So yes—F4 cycles through all four combinations. But for our case? We want the rate in B1 to stay fixed. So delete C2, type B1, then click on B1 in the formula bar and press F4 once. Now it reads =B2*$B$1.
| Rep Name | Deal Value | Commission Rate (B1) | Formula in D2 |
|---|---|---|---|
| Sarah Chen | $124,500 | 7.5% | =B2*$B$1 |
| James Tan | $98,200 | 7.5% | =B3*$B$1 |
| Amina Patel | $142,600 | 7.5% | =B4*$B$1 |
Now drag D2 down to D9. Every row multiplies its own Deal Value by the *same* B1—no drift, no typos, no hunting for C2–C9 to update later.
The Result
Here’s what D2:D9 looks like after the fix—clean, consistent, and instantly editable if B1 changes:
| Rep Name | Deal Value | Commission | Notes |
|---|---|---|---|
| Sarah Chen | $124,500 | $9,337.50 | =B2*$B$1 |
| James Tan | $98,200 | $7,365.00 | =B3*$B$1 |
| Amina Patel | $142,600 | $10,695.00 | =B4*$B$1 |
| Kenji Sato | $87,100 | $6,532.50 | =B5*$B$1 |
| Linh Nguyen | $115,300 | $8,647.50 | =B6*$B$1 |
| Rajiv Mehta | $133,800 | $10,035.00 | =B7*$B$1 |
| Yuki Yamada | $91,400 | $6,855.00 | =B8*$B$1 |
| Tariq Ali | $102,900 | $7,717.50 | =B9*$B$1 |
What Could Go Wrong
Three real mistakes I saw last week in a shared budget file:
- Mistake #1: Using
$B1instead of$B$1. The row still shifts when pasted down—so B1 becomes B2, B3, etc. You get zero commissions for everyone after row 1 because B2:B9 are blank. - Mistake #2: Forgetting to press F4 *while the cell reference is selected*. If you just type
$B$1manually, great—but if you click away first, F4 won’t affect it. It only works on active, highlighted references. - Mistake #3: Applying absolute referencing to the *wrong* cell. In our case, we locked B1—but some users lock B2 instead, thinking “I want this row fixed.” Then when they drag down, every formula calculates
B2*$B$1, giving identical commissions to everyone.
Here’s your quick-reference cheat sheet for next time:
| Reference Type | Example | When to Use It |
|---|---|---|
| Relative | B2 | Use when both row and column should change (e.g., summing adjacent columns) |
| Absolute | $B$2 | Use when you need *exactly one cell*, no matter where the formula moves |
| Mixed (column fixed) | $B2 | Use when copying across rows but need same column (e.g., tax rate in column B, applied to C2:C100) |
| Mixed (row fixed) | B$2 | Use when copying across columns but need same row (e.g., headers in row 2, referenced in formulas across columns) |
