What Does the $ Sign Mean in Excel? Absolute References Explained

You write a formula, copy it down the column, and suddenly the results are wrong or full of zeros. The fix is almost always a couple of $ signs. Here's what they do and the quick way to add them.

The problem: references move when you copy

By default, Excel cell references are relative. When you copy a formula, the references move with it.

Say you have prices in column B and a delivery charge in E1. In C2 you type:

=B2+E1

That's right for row 2. But copy it down to C3 and it becomes =B3+E2. Copy it to C4 and it's =B4+E3. The price moving is what you want. The delivery charge moving isn't. E2 and E3 are empty, so the answers are wrong.

The fix: lock the reference with $

Put a $ in front of the column letter and the row number:

=B2+$E$1

Now copy it down. B2 changes to B3, B4, B5… but $E$1 stays exactly the same in every row. That's an absolute reference.

The shortcut: F4

You don't need to type the $ signs.

1. While typing or editing the formula, click on the reference (or just after it).
2. Press F4.

Excel adds both $ signs for you. On a Mac, press Command + T (or fn + F4 on some keyboards). On a laptop you may need Fn + F4.

Press F4 again for mixed references

Each press of F4 cycles through four options:

Press	Reference	What's locked
1st	$E$1	Column and row
2nd	E$1	Row only
3rd	$E1	Column only
4th	E1	Nothing

The $ locks whatever comes straight after it. E$1 means "the column can change, but always row 1". These are called mixed references.

When would I use a mixed reference?

They're perfect for grids where a formula needs one fixed row and one fixed column. A classic example is a times table:

• Numbers 1–10 down column A (A2:A11)
• Numbers 1–10 across row 1 (B1:K1)

In B2 type:

=$A2*B$1

Copy it across and down the whole grid. $A2 always uses column A (but changes row), and B$1 always uses row 1 (but changes column). One formula fills the whole table.

The same idea works for a price grid with quantities down the side and discount rates across the top.

An easier alternative: named cells

If $ signs make formulas hard to read, give the cell a name instead:

1. Click E1.
2. Click in the Name Box (to the left of the formula bar), type Delivery and press Enter.

Now your formula is =B2+Delivery. Named cells are always absolute, so they never move when copied.

Troubleshooting

F4 does something else. On many laptops F4 controls brightness or volume. Press Fn + F4, or turn on Fn Lock.

F4 repeated my last action instead. You pressed it when you weren't editing a formula. Outside a formula, F4 repeats your last action.

Everything shows the same answer. You've locked something that should move. Remove the $ from the reference that needs to change.

Wrapping up

If a copied formula goes wrong, look for a reference that should have stayed put, click on it and press F4. The $ sign means "don't move this bit".