Absolute references in Excel: the $ sign explained
Copying a formula and getting a wall of wrong answers or errors is a classic sign of a reference problem. Excel tests often check this skill indirectly, by asking you to fill a formula down a column, so it's worth getting right.
Three kinds of reference
| Reference | Type | When you copy it |
|---|---|---|
| B2 | Relative | The column and row both change |
| $B$2 | Absolute | Nothing changes |
| B$2 | Mixed | The row stays and the column changes |
| $B2 | Mixed | The column stays and the row changes |
The $ sign locks whatever comes right after it. You can type it yourself, or press F4 while editing a reference on Windows to cycle through the options.
When you need it
Suppose a tax rate sits in cell E1, and you want tax for each order in column B. In C2, you might write =B2*E1. It works on that row. Copy it down, though, and the next row becomes =B3*E2, which points at an empty cell.
The fix is =B2*$E$1. B2 should move with each row, and the tax rate shouldn't. A simple question to ask is: when I copy this, should this reference move? If not, lock it.
You'll need the same thing in lookups, where the table range should stay put: =VLOOKUP(A2, $F$2:$H$20, 3, FALSE).
Where mixed references come in
Mixed references are useful in a grid, like a multiplication table. With numbers across row 1 and down column A, =$A2*B$1 in cell B2 can be copied in any direction. It always uses the row label from column A and the column heading from row 1.
That's a good one to try yourself. If you can build it from memory and fill it across and down correctly, you've got the idea.
More guides
DeskReady is an independent practice tool, not affiliated with any software vendor or employer. Tests vary, so use this as a general guide.