Excel Pivot Table Exercises: Guided Practice Tasks and Answers

Charts & pivot tables · Updated November 21, 2026

Excel PivotTable exercises help you practise summarizing and exploring data without writing formulas. Start with the sample sales records below, create a PivotTable using Insert > PivotTable, then try the tasks and compare your results with the worked answers.

Why practise with PivotTables?

A PivotTable reorganizes a list of records into a summary. You choose which fields appear as rows, columns, filters, or values, and Excel calculates totals or other summaries for you. Moving a field from Rows to Columns can turn a list of sales by product into a region-by-product comparison without changing the original data.

Practice is useful because the main skill is not memorizing a formula: it is choosing a useful view and checking that the summary matches the question. PivotTables are commonly used to review sales, expenses, survey responses, inventory, and other structured lists. They are especially helpful when a report needs to be rearranged as new questions arise.

Prepare a small dataset for practice

Copy these records into a worksheet, with the field names in the first row. Each row represents one order line; Sales is the total value of that line. The dates are in 2025, and the figures are illustrative rather than real business data.

Date Region Rep Product Units Sales
2025-01-05 North Ana Laptop 2 2400
2025-01-08 South Ben Monitor 3 900
2025-01-12 East Cara Laptop 1 1200
2025-01-18 West Dev Keyboard 5 250
2025-01-22 North Ben Monitor 2 600
2025-02-02 South Ana Laptop 1 1200
2025-02-05 East Dev Keyboard 4 200
2025-02-09 West Cara Monitor 1 300
2025-02-12 North Dev Keyboard 6 300
2025-02-17 South Cara Monitor 2 600
2025-02-22 East Ana Laptop 2 2400
2025-02-27 West Ben Laptop 1 1200

Keep each column to one kind of information, use a distinct heading for every column, and avoid blank rows or merged cells within the list. These habits make it easier for Excel to identify the source and for you to interpret the results. For practice, you can select the range and choose Insert > Table; confirm that the table has headers. An Excel Table can expand when you add records, although you still need to refresh a PivotTable to include new data.

Create your first PivotTable in Excel

  1. Select a cell in the data, or select the full range if it is not an Excel Table.
  2. Choose Insert > PivotTable. Check that the proposed table or range is correct, choose New Worksheet, and select OK.
  3. In the PivotTable Fields pane, drag Product to Rows and Sales to Values. Excel should show Sum of Sales by product.
  4. Check the grand total and compare it with the source: the twelve Sales values add up to 11,550.

In Microsoft 365 for Windows, the PivotTable Fields pane normally appears beside the new report. If it is hidden, select a cell in the PivotTable and use PivotTable Analyze > Field List. In Excel for Mac, the same basic workflow is available, though ribbon placement and pane layout can vary; use Insert > PivotTable and the field list. Excel for the web also supports PivotTables, but its ribbon and available controls can differ from the Windows desktop instructions.

Exercises with worked answers

Try each task before reading its answer. To change a report, drag fields between the Rows, Columns, Values, and Filters areas in the field list. You can reuse the same PivotTable and rearrange it, or insert a separate PivotTable to keep each result visible.

  1. Exercise 1: Total sales by product. Put Product in Rows and Sales in Values. The answer is Laptop, 8,400; Monitor, 2,400; Keyboard, 750. The grand total is 11,550. If your report is different, check that Values is set to Sum of Sales rather than Count of Sales.
  2. Exercise 2: Compare products across regions. Put Region in Rows, Product in Columns, and Sales in Values. North totals 2,400 for laptops, 600 for monitors, and 300 for keyboards, or 3,300 overall. South totals 1,200 for laptops and 1,500 for monitors, or 2,700. East totals 3,600 for laptops and 200 for keyboards, or 3,800. West totals 1,200 for laptops, 300 for monitors, and 250 for keyboards, or 1,750. A blank cell means there is no matching order in this dataset, not necessarily that sales were zero in the real world.
  3. Exercise 3: Rank sales representatives and count their order lines. Put Rep in Rows, then add Sales to Values and add Sales to Values a second time. For the second value field, open its value-field settings and choose Count. The totals are Ana, 6,000; Ben, 2,700; Cara, 2,100; and Dev, 750. Each has three order lines. Count here means the number of nonblank Sales entries, not the number of units sold.
  4. Exercise 4: Compare months. Put Date in Rows and Sales in Values. Right-click a date in the PivotTable and choose Group, then select Months. January totals 5,350 and February totals 6,200. If dates from multiple years are present, group by Years as well as Months so that January in different years is not combined into one category.
  5. Exercise 5: Find average sales per order line. Keep Product in Rows and Sales in Values, open the value-field settings, and select Average. The average is 1,680 for Laptop, 600 for Monitor, and 250 for Keyboard. The overall average is 962.50, calculated from 11,550 divided by 12 order lines.

Practise filters, sorting, and interactive reports

Once the basic summaries work, use the same data to practise answering narrower questions. Drag Region to Filters and select North to display only its records. Alternatively, use a slicer: select the PivotTable, choose PivotTable Analyze > Insert Slicer, select Region, and choose OK. Clicking a region button filters the report; use the slicer’s clear-filter control to return to all regions.

Try sorting the product totals from largest to smallest by right-clicking a value and choosing a sort option. You can also make a PivotChart from a selected PivotTable using Insert > PivotChart. A chart can make a comparison easier to scan, but check that its categories and values match the summary you intend to communicate. Filters and slicers change what is displayed; they do not edit or delete the source records.

Format and interpret the results

Numbers in a PivotTable should be readable and labeled clearly. To format Sales as currency, right-click a value and choose Number Format, then choose the appropriate format. This is preferable to formatting only the visible cells, because number formatting applied through the value field is more likely to remain consistent when the report changes. Use PivotTable Design > Report Layout to choose a layout such as Show in Tabular Form when a more conventional table is useful.

Read the field names as carefully as the totals. Sum of Sales answers a different question from Count of Sales, while Average of Sales describes the mean order-line value rather than total revenue. A grand total can help catch mistakes, but it does not prove that every field was configured correctly. Check the report against a simple known result, such as the 11,550 total, and confirm that filters are cleared if you expect all records to appear.

Common PivotTable problems and fixes

  • Excel counts instead of sums. If a value field contains text or blanks, Excel may use Count. Open the field’s value settings and choose Sum. Also inspect the source for numbers stored as text, which may need to be converted to numeric values.
  • New records are missing. Select the PivotTable and choose PivotTable Analyze > Refresh. If the source was a fixed cell range and new rows fall outside it, change the source range or convert the data to an Excel Table before building the PivotTable. Refresh updates the summary; it does not always repair an incorrectly defined source range.
  • Dates will not group by month. Check that the source cells contain real Excel dates, not text that merely looks like dates. Blank or invalid date entries can also interfere with grouping. Correct the source values, refresh, and try grouping again.
  • Totals seem duplicated or incomplete. Look for duplicate source records, unexpected filters, blank labels, and rows that do not represent the same kind of record. A PivotTable summarizes the source as supplied; it cannot determine whether two similar rows are accidental duplicates.
  • The report does not update as you edit the source. PivotTables commonly need a refresh after source data changes. Use right-click > Refresh or PivotTable Analyze > Refresh. If automatic refresh behavior matters, check the available PivotTable options in your Excel version rather than assuming edits update the report immediately.

Build a useful practice routine

For a focused session, start with one question, build the smallest report that answers it, and then rearrange the fields to answer a related question. For example, begin with total sales by product, add Region as a row field, and then move Product to Columns. Finally, add a slicer or group the dates. This sequence reinforces how the same fields can provide different views of the same records.

After the sample feels familiar, practise on a copy of a real, non-sensitive dataset with more rows and varied categories. Keep the raw data on its own sheet and avoid manually typing over PivotTable totals. If you need a fixed report layout or a calculation that a standard PivotTable cannot provide, formulas or Power Query may be more suitable; for interactive category summaries, a PivotTable is often the simpler starting point.

Frequently Asked Questions

How do I practise PivotTables in Excel?
Use a clean list with headings, create a PivotTable through Insert > PivotTable, and practise moving fields among Rows, Columns, Values, and Filters. Check each result against a known total or a small hand-calculated example.

What is a good beginner PivotTable exercise?
Summarize a numeric field such as Sales by a category such as Product. Then put a second category, such as Region, into Columns and compare the cross-tab with the original records.

Why does my PivotTable show Count instead of Sum?
Excel may be treating some values as text, or the field may contain blanks or nonnumeric entries. Use the value-field settings to choose Sum and inspect the source data if Sum is unavailable or produces an unexpected result.

Do PivotTables update automatically when source data changes?
Do not assume they do. Refresh the PivotTable after changing its source, and check that the source range includes any new rows. An Excel Table can make an expanding source easier to manage, but refreshing is still important.

Can I use these exercises in Excel for Mac or the web?
Yes. The core tasks—placing fields in report areas and summarizing values—are available in Excel for Mac and Excel for the web. Ribbon labels, pane layouts, and some advanced options may differ from Microsoft 365 for Windows, so follow the equivalent PivotTable controls in your version.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.