When you copy a formula, Excel shifts its references. Adding $ signs locks a reference so it always points to the same cell. F4 adds them for you, with no fiddly typing.
Step by step
- Type or edit your formula and place the cursor on (or just after) a cell reference.
- Press F4 once to lock both column and row: $A$1.
- Press again for A$1 (row locked), again for $A1 (column locked), and once more to return to A1.
Tips & troubleshooting
- On many laptops you need Fn+F4.
- Outside of formula editing, F4 repeats your last action, such as formatting or inserting a row.
What the dollar signs mean
A dollar sign fixes whatever follows it. There are four combinations, and F4 steps through them in this order.
- $A$1, absolute: column and row are both fixed. The reference never changes when the formula is copied.
- A$1, mixed: the row is fixed and the column can change. Use it when copying across for values held in one row.
- $A1, mixed: the column is fixed and the row can change. Use it when copying down for values held in one column.
- A1, relative: both change. This is what Excel gives you by default.
A worked example
Prices are in B2:B50 and the VAT rate is in F1. In C2 you type =B2*F1 and copy it down. C3 becomes =B3*F2, which points at an empty cell, so every row after the first shows 0.
The fix: edit C2, click on F1 in the formula and press F4 once so it reads =B2*$F$1. Now copy it down. B2 moves to B3, B4 and so on, while $F$1 stays put.
Mixed references earn their keep in grids. For a times table with numbers down column A and across row 1, the single formula =$A2*B$1 can be copied to the whole grid.
When F4 does nothing
- You are not editing a formula. Press F2 first. Outside a formula, F4 repeats your last action instead.
- The top row is set to media keys. Hold Fn and press F4, or switch on Fn Lock (often Fn+Esc).
- The cursor isn't touching a reference. Click inside or directly after the reference you want to change. To change several at once, select them in the formula bar and press F4.
Alternatives to dollar signs
A named cell is always absolute and is easier to read. Select F1, type VAT in the Name Box to the left of the formula bar, press Enter, and write =B2*VAT. Table formulas such as =[@Price]*VAT avoid the issue in the same way.
On a Mac
In Excel for Mac press Cmd+T, or F4, to cycle the reference.