To stop a formula from shifting its references when you fill it down or across, add $ signs to make an Excel absolute reference. You can type them or press F4 (Cmd+T on a Mac). This guide covers both methods, the mixed references A$1 and $A1, and why F4 sometimes seems to do nothing.
Why your formula breaks when you drag it
Excel references are relative by default. Microsoft's support page explains it this way: if a formula in C2 refers to A2, Excel reads that as "the cell two columns to the left in the same row." Copy the formula somewhere else and the reference moves with it.
Microsoft's example: copying this formula from D4 to D5
=B4*C4
gives you
=B5*C5
That is usually what you want. It goes wrong when one input should stay fixed, such as a single tax rate or a lookup range that every row should use. Fill the formula down and the reference to that fixed cell slides off it onto empty cells, and your results turn into zeros or errors.
Lock the reference with $
Put a $ before the column letter and before the row number. Per Microsoft, copying this from D4 to D5 leaves it exactly as it is:
=$B$4*$C$4
You only need to lock the part that should stay put. In a typical fill-down, the per-row value stays relative and only the fixed cell gets the $ signs.
Toggle it with F4 instead of typing
Microsoft's steps:
- Select the cell that contains the formula.
- In the formula bar, select the reference you want to change.
- Press F4 to switch between the reference types.
Each press moves the reference to the next type. Microsoft's page doesn't give the order. Exceljet says it starts A1 → $A$1 and cycles back to relative. A Mac-focused blog gives the full sequence as $A$1, A$1, $A1, A1. If you overshoot, keep pressing until you get back to the one you need.
The Mac shortcut is Cmd+T
Microsoft's page doesn't mention the Mac. Exceljet and Computerworld both list ⌘T on the Mac. Exceljet says earlier Mac versions accept only Command+T, while Fn+F4 also works in Mac Excel 365.
Mixed references: lock only the row or only the column
A mixed reference fixes one half. $B4 locks column B but lets the row change. C$4 locks row 4 but lets the column change. Microsoft says you'll use these "less often," and its table shows what each type does when a formula is copied two cells down and two cells to the right:
| Reference | What's locked | After copying two down, two right |
|---|---|---|
$A$1 |
column and row | stays $A$1 |
A$1 |
row only | becomes C$1 |
$A1 |
column only | becomes $A3 |
A1 |
nothing | becomes C3 |
Mixed references are what you need when one formula fills a grid in both directions. A multiplication table is the classic case. One input has its row headers down a column, so you lock that column ($A2 style). The other has its column headers across a row, so you lock that row (B$1 style). Then a single formula can be filled right and down. Check a couple of cells after filling to confirm each one points at the headers you expect.
F4 not working? Check these first
You're not editing the formula. F4 has two jobs. Microsoft's keyboard shortcuts page says F4 on its own repeats the last command or action. It only cycles reference types when a cell reference or range is selected in a formula. So if you click a cell and press F4, Excel repeats whatever you did last, such as a format, instead of adding $ signs. Click into the formula bar (or double-click the cell) and put the cursor in the reference first.
You expected it to fix every reference at once. It doesn't. Select one reference, press F4, then move to the next. You can also highlight a whole range reference such as a lookup range and toggle it in one go, since Microsoft says F4 works on a selected range.
Your laptop's F-keys do something else. On keyboards where the top row controls brightness or volume, you may need to hold Fn with F4. Maccove gives this advice for Macs. On a Mac you can skip the function key entirely and use Cmd+T.
You're on a Mac and want F4's other job. The same blog says Microsoft gives no Mac shortcut for repeating the last action, so don't expect Cmd+T to do double duty.
Which Excel versions this applies to
The $ syntax works the same in all of them, so typing the signs by hand is the fallback when a shortcut isn't available.
If you're locking a lookup range so it doesn't shift as you fill down, the same rule applies inside INDEX and MATCH formulas. And if "lock a cell" for you means stopping people from editing it, that's sheet protection, not $ signs. See how to lock only some cells in Excel.