Typing dollar signs by hand is a tax on every formula you write. F4 eliminates it - press it while your cursor is on any cell reference and it cycles through all four reference modes instantly. Three minutes to learn; muscle memory after that.
How to do it
- Start typing a formula, or press F2 to edit an existing one.
- Click on a cell reference in the formula bar - something like
A1orB3. - Press F4.
- The reference cycles:
A1→$A$1→A$1→$A1→A1. - Stop when you have the mode you want and press Enter.
Works mid-formula without leaving the cell.
Why it works
Each mode tells Excel what to lock when you copy the formula:
- Relative (
A1): nothing locks - row and column both shift. - Absolute (
$A$1): both row and column stay fixed. - Row locked (
A$1): row stays fixed, column shifts. - Column locked (
$A1): column stays fixed, row shifts.
The dollar sign is an instruction: "don't move this part when I copy." F4 cycles through the four combinations so you don't have to type them.
When not to use it
On a Mac, F4 triggers Mission Control by default and does nothing in Excel. Use Cmd+T instead, or remap Mission Control in System Settings once and forget about it.
If you want to see absolute and mixed references doing real work, the XLOOKUP dashboard build landing 14 July shows them throughout - locking the lookup range while the formula copies across columns.