Excel Relative and Absolute References

Relative References: The Default Behavior

When you type a plain reference like A1 or B2 into a formula, Excel treats it as relative. That means the reference is stored as a set of directions from the cell containing the formula, not as a fixed address. If the formula in D2 is =B2*C2, Excel actually remembers it as "multiply the cell two to the left by the cell one to the left," not literally "B2 times C2." Copy or drag that formula down to D3, and it automatically becomes =B3*C3, then =B4*C4 in D4, and so on.

' Formula typed once in D2:
=B2*C2

' After copying D2 down to D3 and D4, Excel adjusts it automatically:
D3: =B3*C3
D4: =B4*C4

This is exactly what you want when every row needs the same calculation applied to its own data, such as multiplying each product's price by its own quantity.

Absolute References: Locking a Cell with $

Sometimes a formula needs to keep pointing at the exact same cell no matter where it is copied, for example a single tax rate or exchange rate stored once and used by every row. Adding a dollar sign before the column letter and the row number, like $A$1, tells Excel to freeze that reference completely. No matter where you copy the formula, $A$1 always means cell A1.

Mixed References

A mixed reference locks only the column or only the row, using a single dollar sign in front of whichever part should not change. This is useful in tables where you want a formula to always read from one column while the row still moves, such as always referring to column B's total regardless of which row the formula is copied into.

' Commission rate stored once in B1 (for example, 0.08)
' Sales amounts listed in A2:A10
' Formula entered in C2 and copied down to C10:
=A2*$B$1

' A2 shifts to A3, A4... as it copies down
' $B$1 never changes, so every row uses the same rate

As a rule of thumb: use relative references for anything that should change per row or column (like each product's own price), and use absolute references for any single, shared value the whole sheet depends on (like one tax rate, one exchange rate, or one lookup table).