Relative and absolute references
Copy a formula down and its references move. Learn when that helps, and how dollar signs lock a cell such as a VAT rate.
In this room: 3 tasks, 9 questions
- What happens when a formula is copied2 questions
- Locking a cell with dollar signs5 questions
- One cell, one change2 questions
A formula can be copied down a column, so it is typed once and used on every row.
When a formula is copied down one row, every cell reference in it moves down one row too. This is a relative reference, and it is how a reference behaves unless you lock it.
So =B2*C2, copied from D2 to D3, becomes =B3*C3.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Boxes | Price | Line total |
| 2 | Copier paper | 12 | 4.50 | 54.00 |
| 3 | Envelopes | 8 | 3.20 | |
| 4 | Ring binders | 5 | 2.75 |
Answer the questions below
1 more question follows in this task.
Sometimes every row needs the same cell, such as the one cell that holds the VAT rate. A relative reference would move off it.
- B1
- a relative reference: it moves when the formula is copied
- $B$1
- an absolute reference: it always means cell B1
VAT is 20% here, and the rate is in cell B1. A cell that shows 20% holds the number 0.2.
| A | B | C | |
|---|---|---|---|
| 1 | VAT rate | 20% | |
| 2 | Item | Net | VAT |
| 3 | Copier paper | 54.00 | |
| 4 | Envelopes | 25.60 | |
| 5 | Ring binders | 13.75 |
Answer the questions below
4 more questions follow in this task.
With the rate in one cell, a change of rate is typed once. Every formula that points at $B$1 is worked out again.
A rate typed into each formula, as in =B3*0.2, has to be found and changed row by row.
| A | B | C | |
|---|---|---|---|
| 1 | VAT rate | 20% | |
| 2 | Item | Net | VAT |
| 3 | Copier paper | 54.00 | |
| 4 | Envelopes | 25.60 | |
| 5 | Ring binders | 13.75 |
Answer the questions below
1 more question follows in this task.