# Why does my formula give wrong results when copied down?

A reference that should stay fixed moves as you copy. Lock it with $: =B2-$F$1.

**Excel:** `=B2-$F$1` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Rep | Sales | vs target |  | Target | 50000 |
| Ana | 62000 |  |  |  |  |
| Ben | 48500 |  |  |  |  |
| Cara | 71200 |  |  |  |  |
| Dev | 50000 |  |  |  |  |
| Eli | 39900 |  |  |  |  |

Formula in C2, filled to C6: `=B2-$F$1`

Result: $12,000; -$1,500; $21,200; $0; -$10,100

## How it works

Copied down, a relative reference like F1 moves with the formula: in the next row it becomes F2, which is empty, so every rep after the first is compared with 0. The $ signs in `$F$1` pin the reference, so every row subtracts the same target, while `B2` still moves to each rep.

## Other ways

- `=B2-F$1`: Locking only the row is enough when you fill down. Lock the column too if you also fill across.

## Common mistakes

- Press F4 after a reference to cycle through $F$1, F$1, $F1 and F1.
- Lock only what must stay put. `$B$2` would compare the first rep on every row.
- A named cell (Formulas, Define Name) is another way to refer to one fixed cell.

## Questions

**What is the difference between $F$1, F$1 and $F1?**

$F$1 never moves, F$1 keeps its row, and $F1 keeps its column.

**Why does every row show the same result?**

The opposite mistake: a reference that should move, like B2, was locked.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/formula-wrong-when-copied-down