1
F2Put the formula in edit mode
Enter the target cell and inspect the references.
absolute referenceF4formulas
Practical Guide to Switching to Absolute References with F4
Most formula drift comes from insufficient reference locking. Mastering F4 alone raises both the speed and reliability of formula work.
Shortcuts you will master in this article
F4 / $A$1 / Ctrl + D
If references drift, copy shortcuts are wasted
Even if you extend formulas efficiently with Ctrl+D or Ctrl+R, drifting references force a redo. That is exactly why F4 reference locking must be learned alongside copy shortcuts.
The key is being intentional about what to fix and what to allow to move.
Flow for switching references with F4
Remembering that it is a key you press while editing a formula prevents confusion.
2
← → to navigate to the referenceSelect the cell reference you want to lock
Position the cursor on a reference like A1 within the formula.
3
F4Toggle between relative and absolute reference
Each press cycles through $A$1, A$1, $A1, A1.
4
Ctrl + D / Ctrl + RVerify the result after copying
Actually extending the formula is the fastest way to confirm the locking is correct.
Excel Shortcut Practice
Master Excel shortcuts and
gain real productivity skills
Reading alone won't make them stick. Use KeyboardGym's Excel practice mode to actually type the shortcuts from this article and build lasting muscle memory.
Situations where absolute references are required
Essential when referencing a fixed cell for values like a tax rate, baseline figure, or conversion rate.
Mixed references — locking only the row or only the column — should be tailored to the shape of the table.
Building the habit of pressing F4 before copying formulas prevents accidents.
Related Shortcuts
Visit each shortcut detail page to see key positions and usage tips.
| Key | Action |
|---|---|
F4 | Toggle Reference Type |
F2 | Edit Cell |
Ctrl + D | Fill Down |
Ctrl + R | Fill Right |
Frequently Asked Questions
Q. What does the $ sign mean?
A. It means that reference should stay fixed even when the formula is copied. You can lock just the column, just the row, or both.
Q. F4 is triggering Repeat Last Action instead.
A. In normal mode F4 repeats the last action; inside a formula it toggles references. Make sure you are in cell edit mode.
Q. Which is the default — absolute or relative?
A. Most references start as relative. Apply absolute only to anchor cells you want to fix.
Q. How do I memorize Excel shortcuts faster?
A. Reading alone won't make them stick. Use KeyboardGym's Excel practice mode to actually type the keys and alternate between sequential and random practice for faster retention.
Excelスキルを年収アップにつなげる方法 — 転職・評価アップへの活かし方を解説
解説記事を読む →