ArumSoft Learning
한국어

Intermediate Excel · Interactive lesson 2 of 12

Relative, absolute & mixed references

A plain reference like B2 shifts when you copy it — sometimes that's exactly what you want, and sometimes you need one part of a formula pinned to a single cell no matter where the formula goes. That's what the $ is for.

IntermediateInteractive

Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.

Written tutorialRelative, Absolute and Mixed References in Excel

Skill check

Try these first:

  • Explain what changes automatically in =B2*C2 when it's copied down a column.
  • Write a formula that locks one cell with $B$1 while everything else shifts normally.
  • Explain what a mixed reference like $A2 or B$1 locks — and what it leaves free.
  • Spot a copied formula pointing at the wrong row because a reference wasn't locked.

All familiar? Skip ahead to I3 — Logic Formulas.

Targeted teach

What you're learning

Relative — B2
Shifts with the formula. Copy it down one row and it becomes B3 — exactly what you want for "this row's own numbers."
Absolute — $B$1
Locked on both column and row. Copy the formula anywhere and it still points at B1 — the right choice for one shared constant, like a tax rate.
Mixed — $A2 or B$1
Locks only one half. $A2 keeps the column fixed but lets the row shift; B$1 does the reverse — handy for a small grid like a multiplication table.
Common mistake
Forgetting the $ on a constant — the formula "works" on row 2, then silently drifts to an empty cell by row 10.
Named ranges
Naming a cell (Formulas → Define Name) lets you write =C2*TaxRate instead of =C2*$F$2 — same locking behavior, easier to read.

Reference cell

F2
Tax rate — 8%. Locking it as $F$2 keeps every row pointing at this one cell, even after the formula is copied down.

Where to find it in Excel

Formula bar›F4›Formulas › Define Name

Swipe sideways to see the whole window.

  1. 1$ locks the column and row. Copy this down and B4 moves while $B$1 stays.
  2. 2The one shared input that every row points to.
  3. 3F4 toggles between relative, absolute and mixed references.
  4. 4Formulas › Define Name gives B1 a name such as TaxRate.
  5. 5Or type the name in the Name Box with B1 selected.

Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.

Guided practice

Write the Total formula for the first row using an absolute reference to the tax rate, then fill it down.

🧾 Price × Qty + tax — write row 2, then fill down
—
ƒx
Write =B2*C2*(1+$F$2) in D2 — Price × Qty, plus the locked tax rate.
Tip: press F4 right after typing a reference to cycle through B1 → $B$1 → B$1 → $B1 without retyping dollar signs by hand.

Independent variation

Build a real pricing table

Build a table with at least five items, each with its own price and quantity, plus one shared tax or discount rate stored in a single cell. Write the total formula once with the rate locked by $, fill it down the whole table, and confirm every row still references that same rate cell.

Spot a mistake or have an idea for this lesson? Send feedback