Every Excel formula you’ll ever write points at other cells. The $ sign controls how it points — and it’s the first thing in Excel that people learn as folklore (“just put dollars on it until it works”) instead of as the simple idea it actually is.

Here’s the idea, once and properly.

A reference is a set of directions, not an address

When you write =B2*C2 in cell D2, Excel doesn’t store “B2” and “C2”. It stores “two cells to my left, times one cell to my left.” Directions, relative to where the formula lives.

That’s why copy-down works at all: paste the formula into D3 and the directions replay from the new starting point — =B3*C3. Nobody “changed” your formula. It said relative to me all along.

RELATIVE — directions replay D2 =B2*C2 D3 =B3*C3 D4 =B4*C4 copy down → rows move ABSOLUTE — $E$1 stays put D2 =C2*$E$1 D3 =C3*$E$1 D4 =C4*$E$1 E1 20% every row → same cell
Copy-down replays directions. The $ pins a direction so it can't move.

The $ is a pin

Sometimes you don’t want a direction to replay. Classic case: a VAT rate sitting in one cell.

=C2*$E$1

$E$1 means “always E1, no matter where this formula gets copied.” The $ pins whatever sits immediately after it:

You write Pinned Copied down Copied across
E1 nothing becomes E2 becomes F1
$E1 the column becomes $E2 stays $E1
E$1 the row stays E$1 becomes F$1
$E$1 both stays $E$1 stays $E$1

That’s the entire system. Four variants, one rule: the dollar pins the thing after it.

The everyday recipe

For 95% of real work you need exactly two moves:

  1. Formulas that walk down a list — no dollars at all. =B2*C2, copy down, done.
  2. Formulas that share one setting (a rate, a threshold, a date) — pin it fully: $E$1. Better again, give the cell a name (vat_rate) and write =C2*vat_rate — names are absolute by nature and the formula reads like a sentence.

The mixed forms ($E1, E$1) earn their keep later, in two-way grids — one formula copied both down and across, like a times table or a loan matrix. When you get there: pin the column of your row labels, pin the row of your column labels. Until then, don’t worry about them.

The F4 habit

While editing a formula, tap F4 with the cursor on a reference and Excel cycles it: E1 → $E$1 → E$1 → $E1 → E1. Faster than typing dollars, and it makes experimenting cheap. (Mac: ⌘T.)

When a formula “breaks” on copy

Next time a copied formula returns nonsense, don’t stare at the maths — read the references. Nearly always it’s one of two stories:

  • A shared setting wasn’t pinned, so each row is pointing one cell further away from it (the tell-tale: the first row is right, the rest drift).
  • Or something was over-pinned, so every row is reading the same data row (the tell-tale: identical answers all the way down).

Both are five-second fixes once you can read the directions. That’s the real skill — not memorising dollar patterns, but being able to ask any formula: where do you think you’re pointing?

It’s stage one of the path for a reason. Every lookup, every SUMIFS, every model you’ll ever build stands on this.