The guide · Mixed references
When one kind of $ isn't enough
In the absolute reference challenge, every row used the same tax rate, so $F$1 locked it completely. A chart is different. Each cell needs its own bill, from column A on its row, and its own rate, from row 1 in its column. Lock both references fully and every cell shows the same number. Lock nothing and the references wander off. You need to lock half of each.
Read the $ as "stay"
The $ sits in front of the part that must not change: the column letter, the row number, or both.
| Reference | Copied right | Copied down |
|---|---|---|
$A2 | stays in column A | row changes |
B$1 | column changes | stays in row 1 |
$A$2 | stays A2 | stays A2 |
A2 | column changes | row changes |
Worked example: a tip chart
The bills are in A2:A6 and the tip rates in B1:E1. In B2, =$A2*B$1 is the $20 bill at 10%: $2.00. Copy it to D4 and it becomes =$A4*D$1, the $50 bill at 18%: $9.00. The $ before the A keeps the bill in column A. The $ before the 1 keeps the rate in row 1. Everything else moves with the cell.
How to fill a grid
- Write the formula in the top-left cell, B2.
- Select B2:E2 and press Ctrl+R to fill right.
- Select B2:E6 and press Ctrl+D to fill down.
Dragging the fill handle right and then down does the same. Then click the cell in the far corner and read its formula. E6 should say =$A6*E$1.
F4 cycles through all four
While editing a formula, put the cursor on a reference and press F4 (on a Mac, ⌘+T in Excel). Each press moves on: A2 → $A$2 → A$2 → $A2 → A2. It works in this practice sheet too.
Common mistakes
- Locking everything.
=$A$2*$B$1copies as itself, so every cell shows $2.00. - Locking nothing.
=A2*B1copied right becomes=B2*C1: it multiplies the tip next door by the next rate. - The $ on the wrong side.
A$2locks the bill's row, so every row uses the first bill. The bill needs its column locked:$A2.
FAQ
- What is a mixed reference in Excel?
- A reference with one dollar sign. $A2 locks the column and lets the row change; A$2 locks the row and lets the column change.
- When do I use $A1 and when A$1?
- Ask which way the formula is copied. A value in a column you must stay in as you copy right, like bills in column A, needs the column locked: $A1. A value in a row you must stay in as you copy down, like rates in row 1, needs the row locked: A$1.
- How do I make a multiplication table in Excel?
- Put 1 to 10 in A2:A11 and again in B1:K1. In B2 write =$A2*B$1, then fill right to K2 and fill down to row 11.
- Why does my formula show the same number in every cell?
- Both references are fully locked, like $A$2*$B$1, so every copy points at the same two cells. Lock only the column of one and the row of the other: =$A2*B$1.