Absolute vs relative references in Excel
A relative reference moves when you copy a formula, an absolute one stays fixed, and a mixed one locks only the row or the column.
When you copy a formula, Excel does not copy the cell addresses literally. It copies the relationship, so a reference to the cell one column left stays “one column left” wherever the formula lands. That is a relative reference.
Often you want the opposite: a reference that keeps pointing at one cell. The dollar sign does that. Knowing which reference needs it is the whole skill.
Steps in Excel for Windows
- Write the formula for the first row. In C2 enter the calculation using normal references, such as
=B2*(1+E1), with the tax rate typed in E1. - Decide what must not move. As the formula is copied down, B2 should become B3, B4 and so on, because each row has its own price. E1 should stay put, because there is only one rate.
- Lock the fixed reference with F4. Click inside E1 in the formula bar and press F4. It becomes
$E$1. The dollar signs lock the column and the row. - Cycle for mixed references. Keep pressing F4 to cycle
$E$1,E$1(row locked),$E1(column locked) and back toE1. - Copy the formula. Fill down or across. Excel adjusts every reference that has no dollar sign by the distance moved, and leaves the locked parts alone.
- Check a copied cell. Click a cell several rows down and read its formula in the formula bar. The price reference should have moved and the rate reference should not have.
Worked example
1. A tax rate that must stay put
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Price | 0.08 | ||
| 2 | Pen | 3 | 3.24 | ||
| 3 | Pad | 4 |
The rate sits in E1 and the price in B2. The formula in C2 locks E1 with dollar signs, so when it is filled down to C3 it still points at E1 while the price reference moves from B2 to B3.
2. What happens without the dollar signs
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Price | 0.08 | ||
| 2 | Pen | 3 | |||
| 3 | Pad | 4 | 4 |
Here the formula was copied from C2 to C3 with a plain E1. Excel moved every reference one row down, so C3 now points at E2, which is empty. The result is the price of 4 with no tax added.
3. A mixed reference for a multiplication table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 1 | 2 | 3 | |
| 2 | 1 | |||
| 3 | 2 | 4 | ||
| 4 | 3 |
In C3 the formula multiplies the number in column A of its own row by the number in row 1 of its own column. The column is locked on the first reference and the row on the second, so one formula fills the whole grid. Here A3 is 2 and C1 is 2.
Common mistakes
- Locking the wrong thing.
$E$1locks the row and the column. If only the column should stay put, use$E1; if only the row,E$1. Locking too much stops the formula from adapting when you fill across. - Forgetting to lock a constant. A tax rate, an exchange rate or a lookup table range copied down without dollar signs slides to empty cells, and the results turn into 0 or #N/A.
- Locking a reference that should move. If the price reference were
$B$2, every row would multiply the first price. All the rows would show the same number. - Typing dollar signs by hand. It works, but F4 is faster and less error-prone. On some laptops the key needs Fn held down.
- Assuming Cut and Paste behaves like Copy. Cutting and pasting a cell moves it and keeps its references unchanged. Only copying and filling adjusts relative references.
On Excel job tests
A task that has one input cell and a column of values, such as a rate or a target, is usually checking whether you lock that cell. The cue is a single value that every row has to use.
A grid that needs one formula across rows and columns, like a multiplication table or a discount matrix, calls for mixed references. If your first row is right and the rest are wrong, the missing dollar sign is the first place to look.
Related guides
Questions
- What does the dollar sign mean in an Excel formula?
- It locks the part of the reference that follows it.
$A$1is fixed in both directions,$A1locks the column, andA$1locks the row. - What is the shortcut for absolute references?
- Press F4 while the cursor is in a reference in the formula bar. Each press moves to the next form:
$A$1,A$1,$A1,A1. - When should I use a relative reference?
- When each row or column has its own matching input, such as a price on the same row. Excel adjusts it for you when you fill.
- Do absolute references change if I insert a row?
- Yes. Dollar signs only stop changes from copying. Inserting or deleting rows still shifts the reference to follow the cell.
Practice this
The free lesson Absolute references ($) has checked exercises for this skill in a working grid.
Practice this skill