Relative & absolute references
Write a formula once, then copy it without moving the wrong input.
By the end, you’ll be able to:
- Predict how a relative reference moves
- Keep a shared input fixed with $ signs
- Use mixed references across a calculation grid
A relative reference moves with the formula
Copy =B2*C2 from D2 to D3 and Excel changes it to =B3*C3. The formula moved one row down, so each relative row reference moves one row down too. Copying one column right changes relative column letters instead.
Think in distances: from D2, B2 is two columns to the left on the same row. Copying the formula preserves that relationship. This makes a single line-total formula useful for hundreds of records.
In Excel, enter a formula, select its cell and drag the small fill handle at the selection’s corner to copy it. Copy and paste also adjusts relative references. Moving a formula by cutting and pasting is different; these exercises simulate copying.
Fix the part that must stay put
The shop keeps a shared tax rate in E2. A formula in C2 should multiply the current row’s price by that rate: =B2*$E$2. Copied to C3, it becomes =B3*$E$2. B2 moves; E2 stays fixed.
A dollar sign fixes the part immediately after it. $E$2 fixes both column and row. It does not turn the value into money or prevent someone editing the cell.
| Reference | Fixed part | One right, one down |
|---|---|---|
B2 | Neither | C3 |
$B$2 | Both | $B$2 |
B$2 | Row 2 | C$2 |
$B2 | Column B | $B3 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Net price | Tax | Tax rate | |
| 2 | Notebook | 100 | 0.1 | ||
| 3 | Pen | 60 |
Worked example: prices down, rates across
With prices in B2:B3 and rates in C1:D1, enter =$B2*C$1 at C2. The price always comes from column B, but changes row as you copy down. The rate always comes from row 1, but changes column as you copy across.
At D3 the formula becomes =$B3*D$1. This combines the second item’s price with the second rate. A fully fixed price would repeat the first item; a fully relative price would drift into the output grid.
Identify which direction each input should move, then add the minimum $ signs needed. Test the first copied cell in each direction before filling a large area.
Check before filling a large range
- Inspect the formula in the first output cell.
- Copy one row down and inspect which references changed.
- If the task fills across too, copy one column right.
- Change the shared input and confirm relevant results update.
On Windows, F4 cycles reference types while you select a reference during formula editing. Some keyboards need Fn+F4; shortcuts vary on Mac and web. Typing the $ signs directly also works.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
Take this with you
Relative references move when copied. Use $ to fix only the row, column or cell that should stay in place.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Technical review: 2026-10-16. Page updated: 2026-10-19. How SheetSprout develops and checks lessons.