Excel functions / References & tools
Mixed references ($A1 and A$1)
Lock only the column or only the row of a reference.
How it works
A mixed reference locks half of the address. $A1 always reads column A, but its row moves. A$1 always reads row 1, but its column moves.
They shine in grids where one input runs down the side and another across the top, such as a price table or a times table. One formula, =$A2*B$1, can be copied across the whole grid and still read the right row heading and column heading in every cell.
Example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Qty | 4 | 6 | 9 |
| 2 | 1 | |||
| 3 | 2 | 12 | ||
| 4 | 3 | |||
| 5 |
Result: 12. Quantity 2 from column A times price 6 from row 1.
Tips
- Put a $ in front of the part that should not move: the column letter, the row number, or both.
Practise Mixed references ($A1 and A$1)
Try it yourself in a real spreadsheet, with instant feedback on every answer.