Cell References — Relative vs. Absolute

Chapter 2 promised this chapter would explain why a formula's cell references "shift with each row" when you drag it down — and that shifting behaviour is exactly what this chapter is about. It turns out that shifting isn't always what you want, and Excel gives you a precise way to control it, one dollar sign at a time. Get this concept solid now, because every function in Chapter 4 onward depends on you being able to predict exactly what a formula will look like after it's been copied.

Relative References: The Default Behaviour

Every reference you type — A1, B7, C12 — is relative by default. A relative reference isn't really stored as "cell A1" at all; it's stored as something closer to "the cell two columns left and one row up from wherever I am." Copy or fill that formula somewhere else, and it re-applies that same relative offset from its new position.

This is exactly the behaviour you want almost all the time — a "double this row's value" formula genuinely should look at that row's input, not always the same fixed cell.

The Problem Relative References Create

Now imagine a worksheet with a single tax rate stored once, in cell B1, and a column of prices in A2:A10 that all need that same rate applied. In C2 you write a perfectly reasonable-looking formula:

Nothing crashes and no error appears — the formula just silently multiplies by whatever happens to be sitting in a cell that has nothing to do with the tax rate. This is precisely the kind of bug this course's own Chapter 1 warned about: Excel will never tell you a number is conceptually wrong, only whether a formula is syntactically valid.

Absolute References: Locking Both Parts with $

An absolute reference pins a reference so it never shifts, no matter where the formula is copied. You create one by putting a dollar sign directly in front of the part you want locked — $B$1 locks both the column (B) and the row (1).

Notice A2 is deliberately left relative — it should shift, since each row needs its own price. Only the tax-rate reference needs locking. This mix of "some parts relative, one part absolute" is completely normal in real formulas.

Mixed References: Locking Only the Column, or Only the Row

A dollar sign locks only whatever it directly precedes — which means you can lock just the column, just the row, or both:

Mixed references earn their keep in a two-directional grid — for example, a multiplication table where row 1 holds the numbers 1–12 across the top and column A holds the numbers 1–12 down the side. A single formula typed once in B2 as =$A2*B$1 (column locked on the row-header reference, row locked on the column-header reference) can be filled both right and down to complete the entire grid correctly, because each dollar sign locks only the one part of its own reference that must never change as the formula spreads in that direction.

F4: Cycling Through Reference Types Without Retyping

You don't need to type dollar signs by hand every time. With your cursor positioned inside a reference in the formula bar (or right after typing one), pressing F4 cycles through all four forms in order:

Hands-On Exercises