1. Absolute references
Great work on reaching the final lesson of the course!
2. Relative references
So far, the cell references we've used have been relative references, because copying the references shifted the referenced cell relative to the copying direction.
But what if we want to refer to the same cell in each formula, or only want the cell references to change when copied in a particular direction?
3. Absolute references
This is where absolute references come in. Absolute references don't change when copied into neighboring cells - they will always point to the same cell.
So what's the advantage of using an absolute reference over simply inserting the cell's value into the formula? Like with other cell references, updating the referenced cell will automatically update any cells that refer to it.
4. Absolute references: fixing columns and rows
Let's calculate the interest on each base payment in F2 to F5, using a global interest rate provided in cell B7.
We want the interest values to adjust to changes in the global interest rate, so using an absolute reference is definitely the best option here.
5. Absolute references: fixing columns and rows
We start our formula with the reference to take the percentage of, in this case, the first base payment.
6. Absolute references: fixing columns and rows
Then, we use an absolute reference to the global interest rate in cell B7. The only difference between a relative reference to B7 and the absolute reference, is the dollar signs before the column and row index. This tells the spreadsheet to lock the column and row of the reference, in other words, it stops the reference shifting in either direction when copied.
7. Absolute references: fixing columns and rows
Cell F2 can then be copied
8. Absolute references: fixing columns and rows
to populate the remaining cells in the column.
9. Absolute references: fixing columns and rows
In each formula of the copied cells,
10. Absolute references: fixing columns and rows
the absolute reference remains unchanged -
11. Absolute references: fixing columns and rows
only the relative reference shifts when copied.
12. Absolute references: fixing rows
There are also occasions where we may want the cell reference to only change in one direction. These are called partial absolute references, as they can only change when copied in a specified direction.
If we fix the row in the reference, this means that the reference will only shift when copied into another column,
13. Absolute references: fixing rows
but won't shift when copied into another row.
14. Absolute references: fixing rows
Let's say that we need to pay 12% tax on any interest, and a global tax rate is provided in C7.
To calculate the tax, we need to multiply the interest values by the tax rate. We could create a new formula, but a more efficient method would be to reuse the formulas from the interest column, which already perform a similar percentage calculation.
15. Absolute references: fixing rows
The formula for the interest column multiplies base payment by the interest rate; if we could shift both references one column to the right, we'd have our formula for the tax. We can do this by using an absolute reference with fixed rows in the interest column; then copying the cells into the tax column.
Let's first edit the absolute reference in F2 to
16. Absolute references: fixing rows
remove the dollar sign in front of the column index, so that only the row is fixed.
17. Absolute references: fixing rows
Fixing the row means that when we copy the new F2 formula down, the interest values don't change.
18. Absolute references: fixing rows
We can then copy the interest column into the tax column by selecting the cells and dragging them by the bottom-right corner.
19. Absolute references: fixing rows
When looking at the cell formulas, we can see that both references shifted one cell to the right,
20. Absolute references: fixing rows
but each cell uses the same tax rate, as the row was fixed in the reference.
21. Absolute references: fixing columns
Fixing columns is the reverse of fixing rows: the reference will shift when copied into another row,
22. Absolute references: fixing columns
but won't change when copied into another column.
23. Absolute references: fixing columns
To fix the column in a partial absolute reference, use a single dollar sign in front of the column index.
In the example shown, the reference remains unchanged when copied into different columns, but changes when copied into different rows.
24. Let's practice!
Time for some practice!