Excel Absolute References with F4: Fix Formulas That Return 0

You write a formula in the first row, fill it down, and the first result is right while the rows below show 0. The formula looks the same in each row, so it isn't obvious what went wrong, and the temptation is to fix rows one by one.

The cause is almost always a cell reference that moved when it shouldn't have.

TL;DR: Edit the formula, put the cursor on the fixed cell reference, press F4 to make it $E$2, then fill down again.

What happens

In the example sheet, each row multiplies an amount by a tax rate stored once, in E2. The first row's formula points to E2. When you fill it down, Excel shifts the references by one row, so the second row points to E3. E3 is empty, which Excel treats as 0, so the result is 0.

That shifting is called a relative reference, and most of the time it's what you want. For a single rate cell, it isn't.

Steps

1. Look at the formula in the second row. The tax rate reference has moved from E2 to E3, an empty cell.
2. Go back to the first row. Press F2 to edit the formula, put the cursor right after the E2 reference, and press F4. It becomes $E$2.
3. Fill down again. Every row now uses E2, and the results are correct.

The dollar signs lock the column and the row. Pressing F4 again cycles through the other forms:

• E$2 locks only the row.
• $E2 locks only the column.

Checked in the free Excel for the web.

When you need each form

• $E$2: one input cell used by every row, like a rate, a fee or an exchange rate.
• E$2 or $E2: formulas filled both across and down, such as a grid that multiplies row headers by column headers.

Common mistakes

• Typing the dollar signs by hand and putting them in the wrong place. F4 puts them where they belong.
• Pressing F4 with the cursor elsewhere. F4 changes the reference the cursor is on or next to, so put it on the right one first.
• Fixing every row individually. Fix the first formula and fill again. Editing rows one by one leaves you with inconsistent formulas.

The same rule in code

The behavior carries into automation. In VBA, assigning one formula to a range writes it relative to each row:

Range("F2:F20").Formula = "=D2*$E$2"

D2 adjusts per row, and $E$2 stays put. With openpyxl you build every formula string yourself, so nothing shifts while writing. The locking matters once someone fills or copies the formula in Excel, and writing $E$2 from the start avoids that surprise.

---

AI-assisted article, reviewed against a walkthrough checked in Excel for the web.