Excel pivot tables practice exercises
5 graded problems with realistic data, from medium to hard.
Not a function, but a tool. Summarizes a raw table by grouping rows into categories and aggregating a value, without writing a single formula. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the Pivot tables guide first: syntax, a worked example and the mistakes people make.
In this set
- Total sales by region
- Average deal size by rep
- Count orders by status
- Cross-tab sales by region and product
- Cross-tab total sales by rep and quarter
The exercises
A raw sales export lists every individual deal, one row per sale, no totals. Build a pivot table showing total sales for each region.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | West | Maya | 320 |
| 3 | East | Owen | 210 |
| 4 | West | Priya | 300 |
| 5 | North | Lucas | 150 |
| 6 | East | Farah | 200 |
| 7 | North | Owen | 90 |
Build the pivot table.
Show a hint
Put Region in Rows, Sales in Values, and set the aggregation to Sum. That groups every deal by its region and adds up the Sales in each group.
Totaling a raw list by category is the single most common reason a pivot table gets built. SUMIF does the same job one condition at a time; a pivot does it for every category at once.
The same kind of raw deal list, this time you need each rep's average deal size, not a total.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Deal Size |
| 2 | Maya | West | 400 |
| 3 | Maya | West | 200 |
| 4 | Owen | East | 150 |
| 5 | Owen | North | 90 |
| 6 | Priya | West | 300 |
| 7 | Priya | West | 500 |
Build the pivot table.
Show a hint
Rep in Rows, Deal Size in Values. But this time the aggregation needs to be Average, not Sum.
Picking the right aggregation is the actual skill here. The same Rows/Values setup gives a completely different, and differently misleading, number depending on whether you sum or average.
An order log lists one row per order. Build a pivot table showing how many orders fall into each status.
| A | B | C | |
|---|---|---|---|
| 1 | Order ID | Status | Value |
| 2 | #101 | Shipped | 80 |
| 3 | #102 | Pending | 45 |
| 4 | #103 | Shipped | 120 |
| 5 | #104 | Late | 60 |
| 6 | #105 | Shipped | 30 |
| 7 | #106 | Pending | 75 |
Build the pivot table.
Show a hint
Status in Rows, and for Values pick any field with the Count aggregation (Order ID works well). Count just tallies how many rows landed in each Status group; it ignores what's actually in the Values field.
Counting rows per category, not totaling anything, is what a pivot table's Count aggregation is for. It's the pivot-table equivalent of COUNTIF.
This time one summary number per region isn't enough. Break total sales down by BOTH region and product in a single grid, the way a real pivot table cross-tab looks.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | West | Widget | 200 |
| 3 | West | Gadget | 150 |
| 4 | East | Widget | 90 |
| 5 | East | Gadget | 130 |
| 6 | West | Widget | 100 |
| 7 | East | Widget | 60 |
Build the pivot table.
Show a hint
Region in Rows, Product in Columns, Sales in Values, aggregation Sum. This is the difference between a one-dimensional pivot and a real cross-tab: two fields laid out on two axes instead of one.
A cross-tab (region down the side, product across the top) is what most people actually picture when they hear "pivot table". It answers "how does X break down by Y" in one grid instead of several separate totals.
Sales are logged per rep per quarter. Build a cross-tab pivot table with rep down the side and quarter across the top, showing total sales in each cell.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Quarter | Sales |
| 2 | Maya | Q1 | 300 |
| 3 | Maya | Q2 | 250 |
| 4 | Owen | Q1 | 180 |
| 5 | Owen | Q2 | 220 |
| 6 | Maya | Q1 | 120 |
| 7 | Owen | Q2 | 90 |
Build the pivot table.
Show a hint
Rep in Rows, Quarter in Columns, Sales in Values, aggregation Sum.
Rep-by-quarter (or rep-by-month) cross-tabs are exactly how sales performance gets reviewed in practice. This is the same shape as the region/product cross-tab, with different fields.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the Pivot tables exercises →