Locking a cell: $ and F4

To convert prices from dollars to euros, you divide each one by the exchange rate. It's handy to keep the rate in a single cell: if it changes, you only need to fix one number. In the table below, the rate is in F1, and the formula =B2/F1 in C2 divides the price of the headphones by it: $92 turns into €80.

For the first row, everything is right. But if you fill this formula down, it breaks:

Why the formula breaks

After filling, C3 and the cells below show the error #DIV/0! instead of prices. This is how Excel tells you that a formula divides by zero or by an empty cell.

When you fill a formula, Excel shifts every address in it. That's exactly what the price needs: each row has its own. But the rate shifts along with the price. In C3, the formula becomes =B3/F2, in C4 =B4/F3, and the rate is only in F1, with nothing below it.

Multiplication is even trickier: multiplying by an empty cell gives no error, it just quietly returns 0.

The $ sign locks an address

To keep an address from changing when you fill a formula, you put dollar signs in it: $F$1. There are two signs, and each has its own job: the sign before F holds the column letter in place, and the sign before 1 holds the row number.

An address like this is called an absolute reference: it points to F1 wherever the formula ends up. An address without signs, like B2, is called a relative reference, and it shifts when you fill. The correct formula for C2 has both: =B2/$F$1. Each row has its own price, but the rate is the same for all of them.

Typing the dollar signs by hand is slow, and it's easy to miss one. The F4 key is easier: double-click the cell with the formula, put the cursor on the address F1 and press F4. The address turns into $F$1. On Mac laptops, press fn + F4.

Pressing F4 again cycles through the other options: F$1, $F1 and F1 with no signs. If you pressed it one time too many, keep pressing until $F$1 comes back.

Fix the sheet below: the formula in it has already been filled down without locking.

Which reference to lock

Lock a reference to one shared cell that every row needs:

• an exchange rate;
• a delivery fee;
• a total that each row is divided by.

References to the row's own data stay without $: they're supposed to shift. If you lock them too, for example by writing =$B$2/$F$1, every row ends up with the price of the headphones.

One sign instead of two

Since each sign has its own job, you can use just one. When you fill down, only the row number in an address changes: F1, F2, F3. When you fill to the right, only the letter changes: F1, G1, H1. The $ sign keeps the part it stands in front of from changing.

See what each sign does:

That gives four versions of an address:

• F1 shifts in both directions;
• $F$1 doesn't shift at all;
• F$1 holds the row number: going down, the address doesn't change, and going right, the letter changes;
• $F1 holds the column letter: going right, the address doesn't change, and going down, the number changes.

Addresses with one sign are called mixed references.

For the rate in the price list above, one sign would have been enough, F$1: the formula was only filled down, and two signs work there too. You need just one sign when a formula is filled in two directions at once.

In the price list below, the item prices go down the rows, and the euro and pound rates are in the first row, each above its own column. One formula, =$B3/C$1, calculated the whole table: it was written in C3 and filled to the right and down.