Why your formula broke when you copied it
Lock a cell with $ so a formula keeps working when you copy it down.
Do this lesson on your computer.
It runs beside a practice Excel in your browser, and every step is checked as you go. Free, with no sign-up.
On your computer, go tolearnford.com/go/GX5R
In this lesson
- A relative reference (F1) moves when you copy the formula.
- An absolute reference ($F$1) stays on F1 wherever the formula lands.
- F$1 locks only the row, $F1 only the column. $F$1 is always safe.
The steps
9 steps: 3 explanations, 4 Do this, 2 questions, then Your turn.
Cheat sheet
- A relative reference (F1) moves when you copy the formula: one row down, one row further.
- An absolute reference ($F$1) stays on F1 wherever the formula lands.
- Press F4 (Windows) or Fn+F4 (Mac) while the cursor is on a reference to add the $ signs. In Excel for Mac, ⌘T also works.
- F$1 locks only the row, $F1 only the column. $F$1 is always safe.
- The symptom to remember: a copied formula gives a different, often zero, result in every row.
- Excel and Google Sheets work the same way here; only the Mac shortcut differs.