BenchmarkPrep › Guides
Pivot table practice: exercises with answers
Pivot tables come up in analyst-level Excel tests, and they're quick marks once you know the steps. Here's how they work, the tasks you'll be asked, and exercises with answers.
Updated 6 November 2026
What a pivot table does
A pivot table summarises a list in a few clicks: total sales by region, number of orders per rep, average spend per product. You choose a field for the Rows, optionally one for the Columns, and a Values field with how to summarise it (Sum, Count, Average, Max or Min).
How to make one in Excel
- Click any cell in your data. The data needs a header row and no completely empty rows or columns.
- Go to Insert → PivotTable, check the range, and choose where to put it (a new sheet, or a cell on the existing sheet if the task says so).
- In the field list, drag a field to Rows and a number field to Values.
- To change Sum to Count or Average, click the field in Values and choose Value Field Settings.
Our practice tests have a PivotTable button that works the same way: pick the range, where to put it, and the Rows, Columns and Values fields.
Practice data
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Units |
| 2 | North | Widget | 40 |
| 3 | South | Gadget | 15 |
| 4 | North | Gadget | 22 |
| 5 | East | Widget | 60 |
| 6 | South | Widget | 35 |
| 7 | East | Gadget | 18 |
| 8 | North | Widget | 30 |
| 9 | South | Gadget | 25 |
Exercise 1: total by category
Task: show the total Units for each Region.
Answer: Rows: Region; Values: Units, summarised by Sum.
| A | B | |
|---|---|---|
| 1 | Row Labels | Sum of Units |
| 2 | East | 78 |
| 3 | North | 92 |
| 4 | South | 75 |
| 5 | Grand Total | 245 |
Pivot tables sort row labels A→Z, which is why East comes first.
Exercise 2: count instead of sum
Task: show how many orders each Region had.
Answer: the same layout, but summarise Units by Count: East 2, North 3, South 3, Grand Total 8. A common trap: if the task says "how many orders", Sum of Units is wrong even though the layout looks right.
Exercise 3: a two-way table
Task: show total Units with Region down the side and Product across the top.
Answer: Rows: Region; Columns: Product; Values: Sum of Units.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Row Labels | Gadget | Widget | Grand Total |
| 2 | East | 18 | 60 | 78 |
| 3 | North | 22 | 70 | 92 |
| 4 | South | 40 | 35 | 75 |
| 5 | Grand Total | 80 | 165 | 245 |
Exercise 4: average
Task: show the average Units per order for each Product.
Answer: Rows: Product; Values: Units, summarised by Average: Gadget 20, Widget 41.25.
The mistakes that cost marks
- Leaving rows out of the range. Check the range covers every row of the data, including the header.
- Count when you need Sum (or the other way round). Excel defaults to Count if the value column contains any text or blanks.
- Putting the pivot in the wrong place. If the task names a cell, put the top-left corner there.
- Forgetting to refresh. In Excel, pivot tables don't update by themselves when the data changes: right-click → Refresh.
Frequently asked questions
Are pivot tables in every Excel test?
No. They mostly appear in intermediate and advanced tests, for analyst, finance, data and operations roles. Basic admin tests usually focus on formulas, formatting and sorting.
Do I need to format the pivot table?
Usually not, unless the task asks for it. Tests check that the right fields and summary are used. If it asks for currency, use Value Field Settings → Number Format.
Can I practise pivot tables online without Excel?
Yes. Our free Excel test ends with a bonus PivotTable task in a live spreadsheet, and the Analyst & Finance tests include more.