# Capstone: sales report (Excel lesson 44 of 45)

> Build a report the way you would at work: a calculated column, an input-driven summary and a self-updating table.

Source: https://spreadsheetpractice.com/learn/capstone-sales-report · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Capstones"
- **Time:** about 15 minutes, 3 graded exercises
- **Functions:** `SUMIFS`, `SORT`, `UNIQUE`, `#`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=E2*F2`: Revenue column

## How it works

Real reports chain small skills. First a calculated column. Then a summary that reacts to an input cell. Then a summary table that grows when a new rep appears, with no refilling.

The last step uses the spill reference `#`: `L2#` means "the whole list that spills from L2", however long it gets.

## Syntax

| Formula | What it does |
|---|---|
| `=E2*F2` | Revenue column |
| `=SUMIFS(G2:G13,C2:C13,J2)` | Region total from an input |
| `=SUMIFS(G2:G13,B2:B13,L2#)` | One total per rep, spilled |

## Watch out for

- Point at L2#, not L2:L6. A fixed range misses new reps.

## The exercises

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

1. **Revenue column.** In G2, calculate revenue as units × price. Fill down to G13.
2. **Region summary.** In J3, total revenue for the region typed in J2.
3. **Self-updating rep table.** In L2, list each rep once, A to Z. In M2, total each rep's revenue with one formula that points at the whole spill in L2.

Previous: [Duplicates & reconciliation](https://spreadsheetpractice.com/learn/duplicates-reconciliation-checks) · Next: [Capstone: analyst interview test](https://spreadsheetpractice.com/learn/capstone-analyst-interview)

---

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