How to Lock Cell References with F4 in Excel
To lock a cell reference in Excel, click into the reference inside a formula and press F4. Each press cycles through absolute ($A$1), row-locked (A$1), column-locked ($A1), and back to relative (A1). The dollar sign freezes whatever it precedes so the reference does not shift when you copy the formula. On Mac, use Fn+F4 or Command+T if F4 is mapped to a media key.
What the dollar signs mean
A reference like A1 is relative: copy the formula one column right and it becomes B1, copy it one row down and it becomes A2. That auto-shifting is usually what you want, but not always.
A dollar sign anchors the part it touches. $A1 keeps the column on A while letting the row move. A$1 keeps the row on 1 while letting the column move. $A$1 locks both, so the reference never moves no matter where you copy it.
F4 is simply the shortcut that inserts and rearranges those dollar signs for you, so you never have to type them by hand.
| Press F4 | Reference | Locked | Use case |
|---|---|---|---|
| 0 (typed) | A1 | Nothing | Reference that should follow the fill in both directions |
| 1 | $A$1 | Column and row | A single constant like a tax rate or discount factor |
| 2 | A$1 | Row only | Pulling from a header row across columns |
| 3 | $A1 | Column only | Pulling from a label column across rows |
| 4 | A1 | Nothing (cycle resets) | Back to fully relative |
Each F4 press advances one step. A fourth press returns to a plain relative reference.
Locking a reference step by step
The most common mistake is selecting the whole formula and pressing F4, which only toggles the first reference. You must place the cursor inside the specific reference you want to change.
- Double-click the cell, or select it and press
F2, to enter Edit mode. - Click directly on the reference you want to lock (for example, place the cursor anywhere inside
A1). - Press
F4once for$A$1, twice forA$1, three times for$A1. - Move to the next reference and repeat as needed.
- Press
Enterto commit, then copy the cell to confirm the locked parts stay put.
Mixed references in practice: building a sensitivity table
Mixed references (A$1 and $A1) are the backbone of any data table or sensitivity grid. The classic example is a multiplication table or a two-variable scenario matrix where row inputs run down the left and column inputs run across the top.
If your formula in the top-left output cell is =$A2*B$1, the $A2 locks onto the left input column while the row floats, and B$1 locks onto the top input row while the column floats. Drag that one formula across the entire grid and every cell references the correct pair. Without the mixed locks, the references slide off the inputs and the table fills with garbage.
- Lock the column (
$A2) for inputs that live in a left-hand column. - Lock the row (
B$1) for inputs that live in a top header row. - Lock both (
$E$5) for a single shared constant such as a WACC or growth rate. - Build the formula once in the corner cell, verify the locks, then fill the whole range.
Find Hardcodes
Locking references with F4 keeps formulas portable, but the bigger model risk is a typed-in number hiding where a formula should be. ModelMint's Find Hardcodes scans the sheet and flags every constant buried inside your calculations so nothing silently overrides a locked input.
Get ModelMint See how it worksFAQ
F4 just repeated my last action instead of adding dollar signs. Why?
F4 only cycles references when you are in Edit mode with the cursor inside a cell reference. Outside a formula, F4 repeats the last command. Press F2 first, click into the reference, then F4.
How do I lock a reference on a Mac?
Press Fn+F4 if your function keys default to hardware controls, or use the dedicated shortcut Command+T, which cycles reference types the same way F4 does on Windows.
Should I lock a named range or a table reference with F4?
Named ranges are already absolute by default, so they do not need $ signs. Structured table references like Table1[Amount] also do not take dollar signs. Use F4 for ordinary A1-style references.