Absolute and relative cell references: lock constants with $ when copying formulas

One shared multiplier, three product rows

A small coffee roaster lists single-bag prices in column B and calculates wholesale case prices in column C. Every blend ships in a six-bag case stored once in E2, plus a 3-euro carton fee stored once in F2.

Keeping the pack size and carton fee in dedicated input cells means you can revise packaging rules in one place instead of editing dozens of formulas.

Lock the shared cells before copying down

In C2, multiply the row bag price in B2 by the locked case size in $E$2, then add the locked carton fee in $F$2. B2 stays relative because row 3 must read B3 and row 4 must read B4. The dollar signs before both the column letter and row number keep $E$2 and $F$2 anchored on row 2.

=B2*$E$2+$F$2

Check the copied rows and spot the relative-shift bug

For House in row 2, 8 × 6 + 3 = 51. When you copy the formula to C3 for Single Origin, B2 becomes B3 while $E$2 and $F$2 do not move: 12 × 6 + 3 = 75. Row 4 becomes 10 × 6 + 3 = 63.

Without dollar signs, =B2*E2+F2 still shows 51 in row 2—which makes the mistake easy to miss at first glance. Copied down to row 3, it turns into =B3*E3+F3. Because E3 and F3 are empty, 12 × 0 + 0 returns 0 instead of 75.

When a mixed reference ($B2 or G$1) is cleaner

Sometimes you copy a formula both down rows and across columns—for example, pricing 12-bag club boxes in G1 and 24-bag café crates in H1. Locking only the column in $B2 keeps the bag price in column B as you drag right, while locking only the row in G$1 keeps the header quantity on row 1 as you drag down.

=$B2*G$1

Test a single-cell change across the whole table

In the practice grid, change E2 from 6 to 8 bags per case. House updates from 51 to 67 (8 × 8 + 3), Single Origin updates from 75 to 99 (12 × 8 + 3), and the three-row case total rises from 189 to 249. Setting both E2 and F2 to 0 returns 0, confirming no hidden constants remain inside the formula.

Further reading

• Microsoft: switch between relative, absolute, and mixed references