Home › Excel basics › Absolute references ($A$1)

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

When you copy a formula, Excel adjusts its cell references automatically. Usually that's what you want, but sometimes a reference must stay fixed. That's what the dollar signs in $A$1 are for.

Excel CSE · 3 min read

Relative, absolute and mixed references

The dollar sign "locks" whatever comes right after it: the column letter, the row number, or both.

Relative references: the default

Type =B2*C2 in D2 and copy it down. In D3 it becomes =B3*C3, in D4 =B4*C4. Excel keeps the same relative position: "the two cells to my left". That's exactly right for calculating each row.

Absolute references: lock a cell

Now say every price needs 21% VAT, and the rate is in one cell, F1.

Copied down, B2 becomes B3 and B4, but $F$1 stays $F$1. Without the dollar signs, row 3 would calculate =B3*F2, an empty cell, and return 0.

You'll use this all the time in lookups too: =VLOOKUP(E2, $A$2:$C$7, 3, FALSE) keeps the table in place while E2 moves down. See VLOOKUP.

The F4 shortcut

Click on a reference in the formula bar (or right after typing it) and press F4 to cycle through the four types: A1 → $A$1 → A$1 → $A1 → back to A1. On a Mac, press Cmd+T. On some laptops you need Fn+F4.

Mixed references: a multiplication table

Mixed references shine when you copy a formula both down and across. With the numbers 1–5 in A2:A6 and B1:F1, put this in B2 and copy it to the whole grid:

$A2 always takes the row header from column A; B$1 always takes the column header from row 1. One formula fills the entire table correctly.

Alternatives: names and tables

• Named cells: select F1, type VATrate in the Name Box (left of the formula bar) and press Enter. Then write =B2*VATrate. Names are always absolute and easier to read.
• Excel tables (Ctrl+T) use column names like =[@Price]*0.21, so you rarely need dollar signs.