Pivot table practice: 15 exercises with answers on real sales data

You don't learn pivot tables by reading about them. You learn them by dragging fields around, getting a wrong number and working out why. This page gives you 15 exercises on a year of sales data from an office supplies dealer, from a first total per region to dates rolled up into quarters, percent of total and filters.

Under each exercise you can see the answer, how the fields should be laid out in the pivot table, and a formula that gives the same number. Solve the exercise first, then check. If you're brand new to pivot tables, read the guide to pivot tables first, and come back once you've made your first one.

Get the sales data

The exercises use the sales data from Excel practice data, a free file with 240 orders from 2025. The file has one sheet with the data and one sheet with the exercises and room for your answers. The same exercises are listed below with answers.

Every order has an order ID, a date, a region (North, South, East or West), a salesperson, a product, a category (Furniture, Electronics or Office supplies), the number of units, a unit price and the revenue. That gives you things to group by, dates to roll up and numbers to add up, which is exactly what a pivot table is good at.

Make the pivot table

1. Click a cell in the data on the Sales sheet. You don't need to select all of it, because Excel finds the range on its own.
2. Choose Insert, PivotTable, and click OK to put the pivot table on a new sheet.
3. The PivotTable Fields pane opens on the right, with the columns at the top and four areas at the bottom: Filters, Columns, Rows and Values.
4. Drag a field into an area to build the table. Drag it back out to remove it.

You can use the same pivot table for every exercise. Clear the fields between exercises, or make a new pivot table for each one if you want to look at them again later. Remember to remove the filters from the previous exercise. A forgotten filter is the most common reason for a wrong number.

How to read the answers

Every answer has three parts. The number is what the pivot table should show. Pivot table tells you which fields go in which areas, for example "Rows: Region. Values: Sum of Revenue." Formula is a formula that gives the same number straight from the data. It's there so you can see exactly which rows count, and so you can check your own pivot tables later.

A pivot table often shows more numbers than the exercise asks for. If the exercise asks about the South region, find the South row and read the number in it. If it asks about two things, such as monitors in the North region, read the number where the row and the column meet.

The exercises

One exercise from start to finish

Exercise 5 asks how much Noah Price sold in November. Here's how to solve it step by step:

1. Make a new pivot table on the data, or clear the fields in the one you have.
2. Drag Salesperson to Rows. You get eight rows, one per salesperson.
3. Drag Date to Columns. Excel groups the dates by quarters or months. If you see quarters, click the plus sign next to the fourth quarter to see the months, or right-click and group by Months only.
4. Drag Revenue to Values. Check that it says "Sum of Revenue".
5. Find the row for Noah Price and the column for November. The number where they meet is the answer.

Check the grand total in the bottom right corner. It should be 2,008,750 when nothing is filtered. If it's smaller, there's a filter somewhere, or part of the data isn't inside the pivot table's range.

The same layout answers many other questions at once: who sold the most in a given month, which month was best for each salesperson, and who had a quiet stretch. That's the strength of a pivot table. One layout gives you every combination, and you just read the one you need.

One total per group

Most pivot tables are a total per group: revenue per region, per category or per salesperson. That's exercises 1, 2, 11 and 15. Drag the group to Rows and the number to Values. Excel adds up the numbers and writes "Sum of Revenue" above the column.

The grand total at the bottom is the sum of the whole column. It stays the same no matter which group you split by, so it's a quick check that every row is included. Revenue for the whole year is 2,008,750, and that number should be at the bottom of every pivot table with Sum of Revenue and no filter.

To see the largest group at the top, right-click a number and choose Sort, Sort Largest to Smallest. The sort order sticks when you refresh the pivot table.

Rows and columns at the same time

Exercises 3 and 9 have two conditions: a product and a region. Drag one to Rows and the other to Columns, and you get a cross-table with one cell for each combination. The answer is where the row for the product meets the column for the region.

It doesn't matter which field you put in the rows. Pick the one with the most values for the rows, because a tall table is easier to read than a wide one. Six products in the rows and four regions in the columns gives you a table you can see without scrolling.

The same table built with formulas is a cross-table with SUMIFS and mixed references. Try building it in A cross-table with mixed references, and you'll know what the pivot table is doing for you.

Dates by month and quarter

Five of the exercises ask about a period: a quarter, a month or half a year. Drag Date to Rows or Columns. Newer versions of Excel group the dates into years, quarters and months on their own. If yours doesn't, right-click a date in the pivot table, choose Group, and select Months and Quarters. Select Years too if the data covers more than one year, or January of two different years gets added together.

If Excel won't group, at least one date in the column is text and not a real date. That doesn't happen in the practice file, but it happens all the time with data exported from other systems. See how to find and fix that kind of problem in the guide to dates in Excel.

In a formula, a period becomes two conditions on the date: on or after the first day, and before the first day of the next period. The second quarter is =SUMIFS(I2:I241,B2:B241,">="&DATE(2025,4,1),B2:B241,"<"&DATE(2025,7,1)). The pivot table does the same thing, just without you typing the dates.

Count instead of sum

Exercises 6 and 9 ask how many orders there are, not how much they're worth. Drag Order ID to Values. Because the order ID is a number, Excel adds the IDs together, and that gives you a big number that means nothing. Click the field under Values, choose Value Field Settings, and pick Count.

You can also drag a text field like Region to Values. Excel always counts text, so you get the count right away. Excel does the same thing when a number column has blank cells or text in it. If you see "Count of Revenue" instead of "Sum of Revenue", something in the column isn't a number.

Averages

Exercises 7 and 13 ask for an average. Drag the field to Values and switch to Average in Value Field Settings. The pivot table shows the average with every decimal. Click Number Format in the same dialog box to show fewer, but remember that the exercise asks you to round, and that the number behind the format still has its decimals.

The average in a pivot table is the average of the individual orders in the group, not of the totals. It's the same number AVERAGEIF gives you with a formula.

Percent of total

Exercise 8 asks for a share. Make a total per region, right-click one of the numbers, and choose Show Values As, % of Grand Total. Each region now shows its part of the overall revenue, and the grand total shows 100%. To see both the amount and the share, drag Revenue to Values twice and show only one of them as a percentage.

% of Column Total and % of Row Total do the same in a cross-table, where you see the share within each column or row. There's more on working with shares in calculating percentages in Excel.

Filters and slicers

Exercise 14 is only about one product. Drag Product to Filters, and choose Headset in the list above the pivot table. Every number in the table now covers headsets only. A filter does the same job as an extra condition in SUMIFS.

A slicer is a filter with buttons. Click in the pivot table and choose Insert Slicer on the PivotTable Analyze tab. Pick Region, and you get one button per region, so you can switch with a single click. For dates there's a Timeline, where you drag a bar to pick months or quarters.

Slicers make it obvious what's filtered, and that's a big advantage. A filter in the Filters area is easy to miss when you come back to the pivot table the next day.

The difference between two groups

Exercise 12 asks how much more West sold than East. You can read the two numbers and subtract one from the other, but the pivot table can also do it for you. Right-click a number, choose Show Values As, Difference From, and pick Region as the base field and East as the base item. West now shows the difference from East, and East itself is blank.

The same setting with the date as the base field shows the change from month to month. Choose % Difference From to get the change as a percentage.

Make the pivot table easy to read

Once the numbers are right, four things make a pivot table much easier to read:

• Number format. The pivot table shows 479850 without a thousands separator. Open Value Field Settings, click Number Format, and choose a number with a thousands separator and no decimals. The format stays with the field, even when you refresh.
• Column names. You can rename "Sum of Revenue" right in the cell. The name can't be the same as the field, so write something like "Total revenue".
• Tabular form. Under Design, Report Layout, choose Show in Tabular Form. Each field then gets its own column with a heading, instead of everything being indented under each other.
• Subtotals. With two fields in Rows, you get a subtotal for each group. Turn them off under Design, Subtotals if you only want to see the individual numbers.

Calculated fields

The data has units and revenue, but no average price. A calculated field makes a new column in the pivot table from the other fields. On the PivotTable Analyze tab, choose Fields, Items, & Sets and then Calculated Field. Name the field "Price per unit", and write the formula =Revenue/Units, inserting the fields from the list.

A calculated field works on the totals, not on the individual rows. With products in Rows, it gives exactly the unit price, because every order of the same product has the same price. With regions in Rows, it gives an average price weighted by units, which is exactly what you want.

More questions to ask the data

Once you've solved the 15 exercises, try asking your own questions. These don't have an answer key on this page, but you can check every answer with a formula:

• Which month had the highest revenue, and which had the lowest?
• Which salesperson sold the most furniture, measured in units?
• What share of each region's revenue comes from electronics? Use % of Column Total with categories in the rows and regions in the columns.
• Which three products have the highest revenue? Use Value Filters, Top 10 on the field in Rows, and change 10 to 3.
• How did revenue change from quarter to quarter? Use % Difference From with the previous quarter as the base item.
• Do the regions sell the same products, or does each region have a favorite?

Check the pivot table with a formula

A pivot table can look right and still be wrong, because a filter or an extra field changes the numbers without telling you. So check one number with a formula. Revenue in the South region is =SUMIF(C2:C241,"South",I2:I241), and monitors in the North region are =SUMIFS(G2:G241,E2:E241,"Monitor",C2:C241,"North"). If the formula and the pivot table agree, you know the pivot table is looking at the right rows.

If you click a cell in a pivot table while you're typing a formula, Excel writes a GETPIVOTDATA formula instead of a normal cell reference. It points to the number by the names of the fields, so it finds the right number even if the pivot table gets rearranged. Type the cell reference yourself if you want to copy the formula.

Common mistakes

• Count instead of sum. The column has text or blank cells, or you dragged a text field to Values. Switch it in Value Field Settings.
• A forgotten filter. An exercise gives a number that's too small because the filter from the previous exercise is still on. Clear every filter between exercises.
• The pivot table isn't refreshed. A pivot table doesn't update on its own when the data changes. Right-click and choose Refresh, or press Alt+F5.
• New rows are left out. If you add rows below the data, they fall outside the pivot table's range. Turn the data into a table with Ctrl+T before you make the pivot table, and the range grows with it.
• Dates that won't group. At least one cell in the date column is text. Fix it and refresh.
• Fields in the wrong order. With two fields in Rows, the top one splits the table first. Swap them if the table gets hard to read.

After the exercises

Try the same questions a new way. Make a PivotChart of revenue per month on the PivotTable Analyze tab, and see which quarter stands out. The chart follows the filters and slicers, so you can click through the regions. Which chart type fits is covered in the guide to Excel charts.

Then build one of the pivot tables with formulas: the regions in a column, SUMIF next to them and a total at the bottom. It takes longer, but afterward you know exactly what every cell calculates. The summary table exercises below drill exactly that, from a total per region to a cross-table with COUNTIFS.

Pivot tables in Excel tests

Pivot tables are part of most employer Excel tests, often as "summarize sales by region and quarter" on a file that looks a lot like this one. What gets graded is rarely the pivot table itself, but whether the numbers are right and whether you can answer the question from it. So practice the exercises here against the clock, and read the answer out of the pivot table without working it out by hand. There's more on what gets tested in the guide to Excel for job interviews.

Pivot tables in Google Sheets

The file works in Google Sheets, and you can solve the exercises there too. Choose Insert, Pivot table, and build the table in the editor on the right with the same four areas: rows, columns, values and filters. You switch between sum, count and average under "Summarize by", and you group dates by right-clicking a date in the pivot table. See the other differences in Google Sheets vs. Excel.

In short

A pivot table is a total per group that you build by dragging fields. Put the group in Rows and the number in Values. Use Columns for a cross-table, Group for months and quarters, Value Field Settings for counts and averages, Show Values As for percentages and differences, and Filters or a slicer to look at one part of the data. Always check one number with a formula, clear the filters between exercises, and refresh when the data changes.

If you want to build the same tables with formulas, try the summary table exercises below. The first one is free, and they grade your answer right away.