The dollar sign that fixes everything ($)

This lesson solves the single most common beginner frustration in Excel. If you've ever dragged a formula down and got a column of zeros or #DIV/0! errors, this is why.

The setup

In C3 you write =B3*B1. You get 10.8. Correct.

You drag down to C4. You get 0.

Why it broke

Remember from lesson 1: when you drag a formula down, Excel shifts the references down too. That's usually helpful. Here it's a disaster:

• C3: =B3*B1 ✅
• C4: =B4*B2 ← B2 is the word "Item", not the tax rate ❌
• C5: =B5*B3 ← B3 is 60 ❌

Excel moved both references. You wanted only the first one to move.

The fix

Change B1 to $B$1:

=B3*$B$1

Now drag down:

• C3: =B3*$B$1 ✅
• C4: =B4*$B$1 ✅
• C5: =B5*$B$1 ✅

Perfect.

What each dollar sign locks

The dollar sign locks whatever comes immediately after it.

The multiplication table trick

The classic exercise that proves you understand this. Numbers 1–10 across row 1 and down column A, and one single formula in B2:

=B$1*$A2

Drag it right and down across the whole grid, and you get a complete multiplication table.

Look at why it works: B$1 has the row locked, so it always reads from row 1 as you drag down. $A2 has the column locked, so it always reads from column A as you drag right.

One formula. A hundred cells. That's the whole idea of a spreadsheet in one line.

Names: the tidy alternative

Once you're comfortable, there's a nicer way. Click cell B1, type TaxRate into the Name Box (the little box to the left of the formula bar), and press Enter.

Now you can write:

=B3*TaxRate

It reads like English, it never breaks when dragged, and anyone opening your file six months later immediately understands it. Named cells are one of the clearest signs of a professional spreadsheet.