What Most People Miss About F4 in Excel

A 2024 workplace survey of 1,247 finance and ops professionals found that 73% of Excel users think F4 only repeats the last command — like copy or paste. They don’t realize it’s quietly rewriting cell references every time they press it. That misunderstanding costs an average of 3.2 hours per week when building formulas across dozens of rows.

The Problem

You’re updating a quarterly commission sheet for sales reps at Acme Corp. Column D calculates bonus % based on tier thresholds in cells G2:G5 — but you keep typing $G$2, $G$3, $G$4 manually. Worse, you forget the dollar signs half the time. Then you drag the formula down — and suddenly row 12 points to G13 instead of $G$5. Your bonus totals are off by $18,400. Again.

Here’s what your raw data looks like before fixing:

A	B	C	D (Formula)
1	Sarah Chen	$142,500	=IF(C2>=G2,G3,IF(C2>=G3,G4,IF(C2>=G4,G5,0)))
2	Diego Mora	$189,200	=IF(C3>=G2,G3,IF(C3>=G3,G4,IF(C3>=G4,G5,0)))
3	Priya Patel	$94,800	=IF(C4>=G2,G3,IF(C4>=G3,G4,IF(C4>=G4,G5,0)))
4	Marcus Lee	$211,600	=IF(C5>=G2,G3,IF(C5>=G3,G4,IF(C5>=G4,G5,0)))
5	Tasha Wright	$77,300	=IF(C6>=G2,G3,IF(C6>=G3,G4,IF(C6>=G4,G5,0)))

Notice column D? Every formula refers to G2, G3, etc. — but those are relative. When you drag the formula down from D2 to D10, Excel shifts G2 to G3, then G4, then G5, then G6… even though your tier table stops at G5. You get #REF! errors or wrong bonuses because Excel thinks your lookup table keeps growing.

The Solution

Here’s how to fix this in 4 clicks — no retyping, no dragging, no stress.

Click into cell D2 (where your first formula lives).
Press F2 to edit the formula — or just double-click the cell.
Use your arrow keys or mouse to highlight G2 inside the formula bar (not the cell itself).
Press F4.

That single press changes G2 → $G$2. Press F4 again: $G$2 → G$2 (column relative, row absolute). Press again: G$2 → $G2 (column absolute, row relative). Press once more: $G2 → G2 (back to fully relative). It cycles — four states, four presses.

So for your tier lookup, you want $G$2, $G$3, $G$4, and $G$5. Just highlight each reference in the formula bar and hit F4 once.

Now your corrected formula in D2 looks like this:

=IF(C2>=$G$2,$G$3,IF(C2>=$G$3,$G$4,IF(C2>=$G$4,$G$5,0)))

Drag it down to D6 — and watch what happens. No more shifting columns. No more #REF!. All references stay locked where they belong.

Here’s the cleaned-up result:

A	B	C	D (Fixed Formula)
1	Sarah Chen	$142,500	=IF(C2>=$G$2,$G$3,IF(C2>=$G$3,$G$4,IF(C2>=$G$4,$G$5,0)))
2	Diego Mora	$189,200	=IF(C3>=$G$2,$G$3,IF(C3>=$G$3,$G$4,IF(C3>=$G$4,$G$5,0)))
3	Priya Patel	$94,800	=IF(C4>=$G$2,$G$3,IF(C4>=$G$3,$G$4,IF(C4>=$G$4,$G$5,0)))
4	Marcus Lee	$211,600	=IF(C5>=$G$2,$G$3,IF(C5>=$G$3,$G$4,IF(C5>=$G$4,$G$5,0)))
5	Tasha Wright	$77,300	=IF(C6>=$G$2,$G$3,IF(C6>=$G$3,$G$4,IF(C6>=$G$4,$G$5,0)))

And yes — C2, C3, C4 stay relative (they *should* change as you drag), while G2–G5 stay fixed. That’s exactly what F4 gives you: surgical control over one piece of a formula, not the whole thing.

Going Further

F4 isn’t limited to formulas. Try it anywhere Excel accepts input.

In the Name Box (left of formula bar): Type B2:C10, press Enter, then click back into the Name Box and hit F4 — it becomes $B$2:$C$10. Great for defining named ranges you’ll reuse.
While selecting cells: Select A1:A5, then hold Ctrl + Shift + End to extend to last used row. Press F4 — Excel repeats that entire selection pattern. Works with any multi-step selection (like Ctrl+clicking non-contiguous ranges).
With Alt key combos: After opening the Format Cells dialog (Ctrl+1), pressing F4 applies the last-used number format to new selections. Same for borders, fill colors, fonts.

Here’s a counterintuitive tip: If you type =SUM(A1:A10) and press F4 *before* hitting Enter, Excel adds dollar signs to both A1 and A10 — giving you =SUM($A$1:$A$10). But if you press F4 *after* Enter, Excel won’t touch the formula. You must be editing it.

Also: F4 works inside nested functions. In =VLOOKUP(C2,$A$2:$E$100,3,FALSE), highlight A2:E100, press F4 — all four corners lock at once. Highlight just C2, press F4 — only that reference changes.

When NOT to Use This

F4 is powerful — but misapplied, it creates silent bugs. Avoid it in these cases:

Dynamic array formulas (Excel 365/2021): Functions like SORT(), FILTER(), or SEQUENCE() auto-spill. Adding $ to their range arguments often breaks spill behavior or returns #SPILL!. Let Excel manage the range — don’t force absolutes.
Structured references (tables): If your data lives in an Excel Table named CommData, use [@Sales] or [Tier] — not $B$2. F4 has no effect on those syntaxes.
Named ranges you intend to move: Say you define TierTable = $G$2:$G$5. Later you insert a row above G2 — the name still points to the old location. F4 won’t help. Use OFFSET or dynamic named ranges instead.
When you need mixed locking across sheets: 'Q3 Data'!$B$2 locks both sheet and cell. But if you want the sheet name to change when copying across tabs, don’t use F4 on the full reference — break it up with INDIRECT() or SHEET().

One real-world trap: auditing someone else’s model. You see $Z$99 in a formula and assume it’s intentional. It might just be leftover from F4 spamming — and actually should be Z99 or $Z99. Always verify intent, not just syntax.

Keyboard Shortcuts

F4 shines alongside other shortcuts. Here’s what pairs well with it — tested on Windows (Alt sequences shown):

Shortcut	Action	When to Use It
`F4`	Cycle absolute/relative reference	Inside any formula, in Name Box, or after selection
`Alt` + `H` + `O` + `I`	Auto-fit column width	After pasting wide formulas — makes F4-ed references easier to read
`Ctrl` + ` (grave)	Toggle formula view	Quickly spot which cells have $ signs — no clicking into each one
`Alt` + `E` + `S` + `V`	Paste values only	After using F4 to lock references, avoid pasting formulas with volatile links
`Ctrl` + `Shift` + `Enter`	Legacy array formula entry	If you must use array formulas, F4 locking prevents #N/A when dragging