Absolute vs Relative References in Excel ($A$1 Explained)

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$1 copied down gives the same answer on every row.
  • Typing $ in the wrong place: $A1 vs A$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.

Leave a Comment