Excel practice · Dashboarding

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

  1. Total sales by region
  2. Average deal size by rep
  3. Count orders by status
  4. Cross-tab sales by region and product
  5. Cross-tab total sales by rep and quarter

The exercises

1. Total sales by region Medium

A raw sales export lists every individual deal, one row per sale, no totals. Build a pivot table showing total sales for each region.

ABC
1RegionRepSales
2WestMaya320
3EastOwen210
4WestPriya300
5NorthLucas150
6EastFarah200
7NorthOwen90

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.

2. Average deal size by rep Medium

The same kind of raw deal list, this time you need each rep's average deal size, not a total.

ABC
1RepRegionDeal Size
2MayaWest400
3MayaWest200
4OwenEast150
5OwenNorth90
6PriyaWest300
7PriyaWest500

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.

3. Count orders by status Medium

An order log lists one row per order. Build a pivot table showing how many orders fall into each status.

ABC
1Order IDStatusValue
2#101Shipped80
3#102Pending45
4#103Shipped120
5#104Late60
6#105Shipped30
7#106Pending75

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.

4. Cross-tab sales by region and product Hard

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.

ABC
1RegionProductSales
2WestWidget200
3WestGadget150
4EastWidget90
5EastGadget130
6WestWidget100
7EastWidget60

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.

5. Cross-tab total sales by rep and quarter Hard

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.

ABC
1RepQuarterSales
2MayaQ1300
3MayaQ2250
4OwenQ1180
5OwenQ2220
6MayaQ1120
7OwenQ290

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 →

More dashboarding practice

All Excel practice exercises · Excel test prep