Level 3 · Prices with tax, one rate for every row

In level 1, filling a formula down moved every reference with it: C2 became C3, then C4. That is a relative reference. It means “the cell in this position relative to me”, and it is exactly right for values that live on each row, like a price or a quantity.

Some values are not on each row. A tax rate, an exchange rate or a target lives in one input cell that every row should read. For those you need an absolute reference.

The fill-down trap

Say the rate is in B1 and the first price in C4. This formula is correct in D4:

=C4*(1+B1)

Now fill it down. Excel moves both references by one row each time:

D5   =C5*(1+B2)
D6   =C6*(1+B3)

B2 is empty, and an empty cell counts as 0, so D5 quietly shows the price without tax. B3 holds the header text name, so from D6 down the cells show #VALUE!. Here the error gives the mistake away; in a sheet where the cells under the rate hold numbers, every row would just be quietly wrong while the first row looks perfect. That is why this mistake reaches reports so often.

Locking a reference with $

A dollar sign in front of the column letter or the row number stops that part from moving:

Reference	Filled down	Filled right
B1	B2, B3…	C1, D1…
$B$1	stays $B$1	stays $B$1
B$1	stays B$1	C$1, D$1…
$B1	$B2, $B3…	stays $B1

For a single input cell, lock both parts: $B$1. The fix is:

=C4*(1+$B$1)

Filled down, D5 becomes =C5*(1+$B$1): the price moves, the rate stays.

You do not have to type the dollar signs. Put the cursor on the reference while editing and press F4: Excel cycles through $B$1, B$1, $B1 and back to B1.

Why one input cell

Laura’s request has a second half: “if the rate changes, I want to change one cell”. With the rate in B1, a new rate is one edit and all twenty prices update. If you had typed 1.0825 into every formula, a rate change would mean editing twenty formulas, and missing one. Keeping assumptions in labelled input cells is the habit that makes a sheet trustworthy.

Percent formats

B1 holds 0.0825 and shows 8.25%. The percent format only changes how the number looks; the value is still 0.0825. That is why the formula adds it to 1 directly. If the rate were stored as 8.25, you would need (1+$B$1/100), as with the discount in level 1.

Common mistakes

• Locking the wrong reference. =$C$4*(1+B1) keeps the first price on every row and lets the rate drift. Lock what is fixed, leave what changes per row.
• Locking only the column. $B1 still moves down a row each time.
• Typing the rate into the formula. =C4*1.0825 works until the rate changes.