# Relative vs absolute references (Excel lesson 3 of 45)

> The dollar sign decides what moves when you fill a formula.

Source: https://spreadsheetpractice.com/learn/relative-absolute-references · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Foundations"
- **Time:** about 9 minutes, 3 graded exercises
- **Functions:** `$A$1`, `$A1`, `A$1`
- **Access:** free
- **Key formula:** `=B2*$F$2`: B2 moves, F2 stays

## How it works

When you fill a formula down or across, Excel shifts its references by the same amount. That is why `=B2*C2` becomes `=B3*C3` one row down. These are *relative* references.

A dollar sign locks part of a reference. `$F$2` never moves. `$A2` keeps column A but lets the row change; `B$1` keeps row 1 but lets the column change. In desktop Excel, press F4 (⌘+T on Mac) after a reference to cycle through the options.

Lock any single input every row shares: a tax rate, a total, an exchange rate.

## Syntax

| Formula | What it does |
|---|---|
| `=B2*$F$2` | B2 moves, F2 stays |
| `=B2/$B$7` | Every row divides by the total |
| `=$A2*B$1` | Mixed: column A and row 1 locked |

## Watch out for

- Filled rows that show 0 or #DIV/0! usually mean a reference that should be locked drifted onto an empty cell.
- The grader requires one formula filled across the range, so typing a separate formula in each row won't pass.

## The exercises

Solve them in the live grid at [Relative vs absolute references](https://spreadsheetpractice.com/learn/relative-absolute-references). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Sales tax.** In C2, calculate the tax on each price using the rate in F2. Fill down to C6.
2. **Share of total.** In C2, divide each region's sales by the total in B7. Fill down to C6.
3. **Mixed references.** Fill B2:F6 with one formula that multiplies the number in column A by the number in row 1. Write it in B2, then fill right and down.

Previous: [SUM, AVERAGE, MIN, MAX & COUNT](https://spreadsheetpractice.com/learn/sum-average-min-max-count) · Next: [Percentages & growth](https://spreadsheetpractice.com/learn/percentages-and-growth)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
