Absolute vs relative references

You met relative and absolute references at the essentials level. Now let's master them — because at the intermediate level, mixing them deliberately is what lets you build formulas you write once and copy across a whole grid, and it's the difference between a spreadsheet that's a chore to maintain and one that scales effortlessly. The humble dollar sign is a power tool. Let's use all three modes with intent.

The three reference modes

RECAP + DEEPEN — the $ locks part of a reference when you COPY a formula.

  A1     RELATIVE   — both shift (column + row adjust when copied).
  $A$1   ABSOLUTE   — both locked (never shifts — a fixed cell, e.g. a rate).
  $A1    MIXED      — column locked, ROW shifts.
  A$1    MIXED      — row locked, COLUMN shifts.

$ before the LETTER locks the COLUMN. $ before the NUMBER locks the ROW.
Press F4 on a reference to cycle: A1 -> $A$1 -> A$1 -> $A1 -> A1.

THE INTERMEDIATE SKILL is MIXED references — locking ONE direction. This lets
ONE formula fill a whole 2D GRID correctly. Classic example: a multiplication
table, or applying row rates x column values.

Ask of every reference in a formula you'll COPY: "when this copies, should
this reference SHIFT or STAY — in each direction (across / down)?" The answer
tells you where to put the $.

You know the basics: $ locks part of a reference when you copy — $A$1 is fully absolute (never shifts), A1 is fully relative (both shift). The intermediate skill is the mixed references — $A1 (column locked, row shifts) and A$1 (row locked, column shifts). These let a single formula fill an entire 2D grid correctly. The mental habit: for every reference in a formula you'll copy, ask "when this copies, should it shift or stay — across, and down?" — and put the $ wherever it should stay. F4 cycles through the four modes so you don't type the dollar signs by hand.

Mixed references in action

WHY MIXED REFERENCES MATTER — a grid example.

A MULTIPLICATION TABLE: row headers 1-10 down column A, column headers 1-10
across row 1. The formula in B2 should multiply "this row's header x this
column's header" for the WHOLE grid from ONE formula:

  In B2:  =$A2 * B$1
    - $A2  -> always column A (the row header), but the ROW shifts down.
    - B$1  -> always row 1 (the column header), but the COLUMN shifts across.
  Copy B2 across AND down the whole grid -> every cell is correct.

WITHOUT the mixed $: copying would shift both parts wrongly + the table
breaks.

REAL-WORLD USES:
- Apply per-row rates to per-column periods (a pricing/interest grid).
- A commission grid (salesperson rows x tier columns).
- Any table where each cell = something(row) combined with something(column).

One formula -> a whole grid, thanks to mixed references. This is a signature
intermediate-Excel move.

The classic demonstration is a multiplication table: put row headers down column A and column headers across row 1, then in B2 write =$A2 * B$1 — $A2 always points to column A (the row header) but its row shifts down, while B$1 always points to row 1 (the column header) but its column shifts across. Copy that one formula across and down the whole grid and every cell is correct. Real uses: applying per-row rates to per-column periods (a pricing or interest grid), a commission grid (salesperson rows × tier columns) — any table where each cell combines a row value with a column value. One formula fills the grid — a signature intermediate move.

The mistake beginners make

The intermediate-level mistake is using only fully-relative or fully-absolute references and never mixed ones — so grid formulas can't be written once and copied both directions; you end up writing (and maintaining) a separate formula per row or column. Learn mixed references ($A1, A$1) to fill grids from one formula. The second mistake is guessing where to put the $ and checking by trial and error — instead of reasoning about it ("should this shift across? down?"). Think it through per direction, and use F4 to apply it. The third mistake is breaking a grid by copying without the right locks — copying a formula across a table where a reference shifted that should have stayed (a rate that drifted off its column), producing wrong results that look plausible. Reason about each reference's lock in each direction. Master all three modes — relative, absolute, and especially mixed — and you can copy one formula across an entire grid correctly.

Your turn

---

Your turn

1. Build a 10x10 multiplication table: row headers 1-10 down column A, column headers 1-10 across row 1, and in B2 write =$A2 * B$1. Copy B2 across and down the whole grid and confirm every cell is correct.
2. Break it to learn it: try the same table with =A2*B1 (no dollar signs) and watch the references shift wrongly and the table break. Then fix it with the mixed $ and see it work.
3. Reason about locks: for a formula you'll copy across and down, ask for each reference 'should this shift across? down?' and place the $ to lock whatever should stay.
4. Use F4: in a formula, select a reference and press F4 repeatedly to cycle A1 -> $A$1 -> A$1 -> $A1 — the fast way to set mixed references without typing dollar signs.
5. Apply it for real: build a grid that combines a row value with a column value (e.g. a pricing grid of per-item cost x quantity tiers) using one mixed-reference formula copied across the whole grid.

Key points

• The $ locks part of a reference when you copy: A1 relative (both shift), $A$1 absolute (both locked), $A1 (column locked, row shifts) and A$1 (row locked, column shifts) are MIXED — press F4 to cycle them.
• The intermediate skill is MIXED references — locking ONE direction — which lets a single formula fill a whole 2D GRID correctly (e.g. =$A2 * B$1 for a multiplication table copied across and down).
• Mental habit: for every reference in a formula you'll copy, ask 'when this copies, should it shift or stay — across, and down?' and put the $ wherever it should stay.
• Real uses: per-row rates x per-column periods (pricing/interest grids), commission grids (rows x tiers), any table where each cell combines a row value with a column value — one formula fills the grid.
• The mistakes: using only fully-relative/absolute and never mixed (can't fill grids from one formula), guessing $ placement by trial and error (reason per direction), and breaking a grid by copying without the right locks.