You write a formula, copy it down, and suddenly the results are wrong or show zeros. The reason is almost always cell references. Once you understand the difference between A1 and $A$1, copying formulas becomes easy.
Quick answer
| Type | Looks like | When you copy the formula… |
|---|---|---|
| Relative | A1 |
Column and row both change |
| Absolute | $A$1 |
Nothing changes, it always points to A1 |
| Mixed (lock column) | $A1 |
Column stays A, row changes |
| Mixed (lock row) | A$1 |
Row stays 1, column changes |
Shortcut: click inside a reference while editing a formula and press F4 (Mac: Cmd + T) to cycle through A1 → $A$1 → A$1 → $A1.
Relative references (the default)
In D2 you type =B2*C2 (quantity × price) and copy it down. In D3 it automatically becomes =B3*C3, in D4 =B4*C4, and so on. That’s exactly what you want here, because each row uses its own numbers.
Absolute references: lock a cell
Now you want to add VAT using one rate in cell G1 (10%). In E2 you type =D2*G1 and copy it down. In E3 it becomes =D3*G2, but G2 is empty, so the result is 0. ❌
Fix: lock the rate with dollar signs:
=D2*$G$1
Now every row uses G1. ✅ The dollar sign means “don’t move this part when copying.”
Mixed references: lock only the row or column
Classic example: a multiplication or price table. Numbers 1–10 are in A2:A11 (rows) and 1–10 in B1:K1 (columns). In B2 type:
=$A2*B$1
Copy it across and down. $A2 always reads from column A, and B$1 always reads from row 1. One formula fills the whole table.
A real-life version: quantities down column A, price levels across row 1, and a total for every combination.
Where you’ll need $ most
- Rates and settings: VAT, exchange rate, commission % in one cell
- Lookup ranges:
=VLOOKUP(A2,$F$2:$H$100,3,FALSE). Without $, the range slides down and you get #N/A errors. See VLOOKUP explained. - Totals and percentages of total:
=B2/$B$20. See percentages in Excel. - COUNTIF and SUMIF ranges:
=COUNTIF($A$2:$A$100,A2) - Conditional formatting rules that compare to one cell
Easier alternatives
- Named ranges: name G1 “VAT_Rate” and write
=D2*VAT_Rate. Names are always absolute. See named ranges. - Excel Tables: formulas like
=[@Qty]*[@Price]don’t need $ at all. See Excel Tables.
Common mistakes
- Forgetting $ on a lookup range, so results break further down the list.
- Locking too much:
$D$2*$G$1copied down gives the same answer on every row. - Typing $ in the wrong place:
$A1vsA$1. Ask: “which part must stay the same when I copy?”
Google Sheets
It works exactly the same, including F4 to toggle the dollar signs. See Google Sheets for beginners.
FAQ
What does the $ sign mean in Excel?
It locks part of a cell reference so it doesn’t change when you copy the formula. $A$1 locks both the column and the row.
What is the shortcut for absolute reference?
Press F4 on Windows (Fn + F4 on some laptops) or Cmd + T on a Mac while the cursor is on the reference.
Why does my formula show 0 when I copy it down?
A reference that should stay fixed, like a rate in one cell, is moving to empty cells. Add $ signs to lock it, e.g. $G$1.
When should I use a mixed reference?
When copying a formula both across and down, like a table where one value comes from a column and the other from a row, e.g. =$A2*B$1.