Relative and absolute references

The idea

In module 2 you saw that when you fill =B2*C2 down, it turns into =B3*C3, =B4*C4 and so on. That happens because Excel doesn't really remember "B2". It remembers where B2 is compared to the formula: "the cell two columns to my left, on my own row". When the formula moves down a row, "my own row" moves with it. This kind of reference is called relative, and most of the time it's exactly what you want, because every row should use its own price and its own quantity.

But some numbers aren't per row. Think of a tax rate. There's one rate for the whole shop, typed once at the top of the sheet, and every product's tax should use that same cell. If the reference shifts when you fill the formula down, row 3 goes looking for the rate one row lower, finds an empty cell, and gets it wrong. For cases like this you lock the reference with dollar signs, which makes it absolute: it points at the same cell no matter where the formula ends up.

The problem

Let's see that problem happen for real. This module's sheet is the café's product list again, with a tax rate of 11% in F1 that every product should use:

The tax on a product is its price times the rate. Watch what happens when you fill the obvious formula down:

D1: Tax
D2:D6: =B2*F1

D1: Tax
D2: 2750
D3: 0
D4: 0
D5: 0
D6: 0

Only the first row is right. Kopi Susu's tax is 25000 × 11% = 2750, but every product after it shows 0. Click D3 to see why: it says =B3*F2. The fill moved both references down a row. Moving B2 to B3 was correct, since that's Teh Tarik's price. But moving F1 to F2 sent the formula to an empty cell, and anything times an empty cell is 0.

What makes this mistake sneaky is that the first row looks perfect. If you only check the top of the column, you'd never know the rest is wrong.

Absolute references: $

The fix is to tell Excel that F1 should never move. You do that by putting a $ in front of the part you want to lock. A $ in front of the column letter locks the column, and a $ in front of the row number locks the row. $F$1 locks both, so it stays on F1 wherever the formula goes:

D1: Tax
D2:D6: =B2*$F$1

D1: Tax
D2: 2750
D3: 1980
D4: 2475
D5: 3850
D6: 1320

Now D3 is =B3*$F$1, D4 is =B4*$F$1, and so on. The price reference still moves to each row, but the rate stays put. And because every row reads the rate from the same cell, changing F1 to 12% updates every tax in the column at once.

Shares of a total

Tax rates aren't the only shared number. Another one you'll meet all the time is a total. Say you want to know what share of all the cups sold each product makes up. You work out the total once, in C7, and then every row divides its own quantity by that same total. So the quantity reference should move, and the total should be locked:

# The total quantity, which every share divides by
C7: =C2+C3+C4+C5+C6
G1: Share
G2:G6: =C2/$C$7

C7: 48
G1: Share
G2: 0.25
G3: 0.1875
G4: 0.1458333333
G5: 0.1041666667
G6: 0.3125

48 items were sold in total, and Kopi Susu's 12 of them is 0.25, a quarter of all sales. Pisang Goreng is the biggest seller at 0.3125. Without the dollars, G3 would divide by C8 instead of C7. C8 is empty, so you'd get #DIV/0! from the second row onwards.

Mixed references

So far you've locked both the column and the row. But you can also lock just one of them, which sounds strange until you need it:

Mixed references are useful for two-way tables, where you have values going down the side and values going across the top, and every cell in the middle combines one from each. A price table with products down the side and quantities across the top is a common example. The simplest version is a times table: numbers down column H, numbers across row 1, and every cell multiplies "the number at the start of my row" by "the number at the top of my column":

H2: 1
H3: 2
H4: 3
I1: 10
J1: 20
K1: 30
I2:K4: =$H2*I$1

H2: 1
H3: 2
H4: 3
I1: 10
J1: 20
K1: 30
I2: 10
J2: 20
K2: 30
I3: 20
J3: 40
K3: 60
I4: 30
J4: 60
K4: 90

The trick is that each reference only locks the part that should stay the same. $H2 locks the column, so it always looks in column H, but the row is free to change, so each row finds its own number. I$1 locks the row, so it always looks in row 1, but the column is free, so each column finds its own number at the top. One formula fills the whole 3 × 3 grid. Click K4 and you'll see it became =$H4*K$1: row 4's number times column K's number, 3 × 30 = 90.

A worked example: sales commission

Staff earn a commission on their sales, and the rate lives in one cell so the manager can change it. Here the rate goes in F3, next to a label. Every commission uses the same rate (absolute) and its own row's sales (relative):

E3: Commission rate
F3: 2.5%
H1: Staff
H2: Ayu
H3: Budi
I1: Sales
I2: 4200000
I3: 3150000
J1: Commission
J2:J3: =I2*$F$3

E3: Commission rate
F3: 2.5%
H1: Staff
H2: Ayu
H3: Budi
I1: Sales
I2: 4200000
I3: 3150000
J1: Commission
J2: 105000
J3: 78750

Ayu sold 4200000, so her commission is 4200000 × 2.5% = 105000. Budi's is 78750. When the manager decides to raise the rate to 3%, they change one cell and every commission updates. That's the real reason absolute references matter: every number that's shared lives in exactly one place. If the rate were typed into 200 formulas instead, changing it would mean finding and editing all 200, and missing even one would give someone the wrong pay.

Copying vs moving, again

Everything in this module applies to copy and paste as well, not just the fill handle. When you copy a formula and paste it somewhere else, its relative references shift by however far you pasted it, and its absolute ones stay exactly as they are. Moving is different: cutting and pasting, or dragging the border of a selection, never changes any references, because Excel treats it as moving the data rather than making a new copy.

Recap

• References are relative by default; filling or copying shifts them.
• $ locks what follows it: $F$1 is fully absolute.
• Lock shared inputs (a rate, a total) so every row uses the same cell.
• Mixed references ($H2, I$1) lock only the column or only the row; they fill two-way tables.
• F4 cycles a reference through the four kinds.