Why does your commission formula return 0% when pasted down column C? Why does =A1*B1 turn into =A2*B2 instead of staying fixed on B1? Why did your colleague’s version work fine until she opened it on Mac?
Quick Answer
Press F4 while editing a formula to toggle between relative (A1), absolute ($A$1), and mixed ($A1 or A$1) references. That’s it — no menus, no ribbon clicks. If F4 doesn’t work, check if your laptop uses Fn+F4; try Alt+Shift+4 as backup.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| F4 shortcut | Click inside formula bar, select cell reference (e.g., A1), press F4 | Speed, single-cell edits, keyboard-first users | Only works during formula edit mode — fails if you click away first |
| Manual $ symbols | Type $ before column letter and row number: $A$1 | Teaching beginners, auditing complex formulas, mobile Excel | Easy to miss one $ — causes subtle errors that don’t throw errors |
| Name Manager | Define name (e.g., "TaxRate") for $B$2 → use =A3*TaxRate | Repetitive constants across sheets, audit-ready models | Overkill for one-off references; adds layer of indirection |
| Paste Special → Formulas | Copy cell with absolute ref, paste special → formulas only | Bulk updates across non-adjacent ranges | Doesn’t create new absolutes — only preserves existing ones |
Method 1 Deep Dive
Let’s say you’re calculating quarterly bonuses at Acme Corp. Column A has names, B has base salary, and C needs bonus = B2 * tax rate. That tax rate lives in cell $E$1 — locked so it stays constant when you drag down.
Type =B2*$E$1 in C2. Now drag the fill handle down to C6. Watch what happens:
| Row | A (Name) | B (Salary) | C (Bonus Formula) | C (Result) |
|---|---|---|---|---|
| 2 | Sarah Chen | $82,500 | =B2*$E$1 | $3,300 |
| 3 | James Rivera | $74,200 | =B3*$E$1 | $2,968 |
| 4 | Maya Patel | $91,800 | =B4*$E$1 | $3,672 |
| 5 | Diego Lopez | $66,900 | =B5*$E$1 | $2,676 |
| 6 | Lena Kim | $88,400 | =B6*$E$1 | $3,536 |
Notice how B2 becomes B3, B4, etc., but $E$1 never changes. That’s the core idea: absolute references anchor to one spot. Here’s the counterintuitive part — if you copy C2 and paste into Z100, the formula still reads =$E$1. It won’t shift. That’s why absolute references break if you delete row 1 or column E later. Always test after structural edits.
Method 2 Deep Dive
How to absolute reference multiple cells in Excel
You don’t have to press F4 on each cell individually. Say you’re building a pricing matrix where columns D through G hold region multipliers (D1: US, E1: EU, F1: APAC, G1: LATAM), and rows 2–5 hold product IDs. You want =B2*D1 in D2, then drag across and down — but D1 must stay D1 horizontally, yet become E1, F1, G1 as you move right.
This is where mixed references save hours. In D2, type =B2*D$1. The $ before 1 locks the row — so dragging right gives =B2*E$1, =B2*F$1, =B2*G$1. Dragging down gives =B3*D$1, =B4*D$1, etc. Row stays fixed; column shifts.
Now try this: in D2, type =$B2*D$1. The $ before B locks the column. So dragging down keeps B2, B3, B4 — but dragging right still changes D1→E1→F1. This combo lets you build cross-tab lookups without rewriting formulas.
For full ranges — like locking a lookup table — use $B$2:$D$10 instead of B2:D10 inside VLOOKUP or SUMIFS. Example: =SUMIFS($E$2:$E$100,$A$2:$A$100,A2,$C$2:$C$100,">=2024-01-01"). Every range here is absolute so the entire data block stays put when copied elsewhere.
Pro tip: Select the whole range in the formula bar (e.g., B2:D10), then press F4 once — Excel applies $ to all addresses in that selection. Try it on =SUM(B2:B10,C2:C10): highlight “B2:B10,C2:C10”, hit F4, and it becomes =SUM($B$2:$B$10,$C$2:$C$10).
Cheat Sheet
| Action | Shortcut / Syntax | Example | Notes |
|---|---|---|---|
| Make A1 fully absolute | F4 (or Alt+Shift+4) | $A$1 | Press repeatedly to cycle: A1 → $A$1 → A$1 → $A1 |
| Lock row only | F4 twice | A$1 | Use when dragging down but want column to change |
| Lock column only | F4 three times | $A1 | Use when dragging right but want row to change |
| Absolute entire range | Select B2:D10 in formula bar → F4 | $B$2:$D$10 | Works on comma-separated ranges too: B2,B5,C7 → $B$2,$B$5,$C$7 |
| Check for mixed refs in existing formula | Ctrl+` (grave accent) | Shows =B2*D$1 instead of result | Toggle formula view to audit references fast |
