FormulaCamp
Completed
All interactive exercises
Hands-on Excel exercises

PivotTable Practice

Practice PivotTables with hands-on Excel exercises for rows, columns, dates, percentages, filters, calculated fields, and PivotCharts.

A PivotTable turns a clean list into a summary without writing one formula per category. Good PivotTable practice starts before the pivot itself: the source needs one heading row, consistent columns, and one record per row. Use the live spreadsheet below to explore the same kind of grouped total with a formula, then open the PivotTable tasks to build summaries directly from source data.

The exercises cover a basic row summary, a rows-and-columns layout, date grouping, percentages of a total, report filters, calculated fields, and a PivotChart. When a new record is missing, the likely issue is the source range or a refresh, not the aggregation setting. When a total looks wrong, inspect whether the field is being counted instead of summed.

The worked answers emphasize field placement and validation. Before formatting, state what one output cell means, then trace it back to the matching source records. That habit keeps a compact report auditable. FormulaCamp’s browser engine teaches the workflow with interactive tasks; exact desktop commands can vary by Excel version, but the data-shaping and checking decisions remain the same.

Your turn

Your turn: in F2, total the Software amount as a formula-based check for a category summary.

Example spreadsheet for category summary
ABCDEF
1CategoryAmountCategory
2Hardware1200Software
3Software800
4Hardware600
5Services500
6Software300
Not yet solved.

Related exercises

Open a task in its lesson.

  1. Build a PivotTable: the sum of sales by rep, with the result in F1.

    PivotTable Free

  2. Build a second PivotTable: the average deal amount by city, with the result in F8.

    PivotTable Free

  3. Build a PivotTable in F1: cities in rows, products in columns, the number of deals.

    PivotTable with Rows and Columns Free

  4. Build a PivotTable in E1: the sum of sales by month (dates grouped by month).

    PivotTable: Grouping Dates and % of Grand Total

  5. Build a PivotTable in H5: the share of each channel in sales (% of grand total).

    PivotTable: Grouping Dates and % of Grand Total

  6. Build a PivotTable: the sum of sales by destination. Location: F1 (the window suggests it).

    Practice: PivotTables

  7. Build a PivotTable in F1 by category with a calculated field "Average price" = Revenue / Qty.

    Calculated Fields and Report Filters

  8. Build a PivotTable in F1: the sum of revenue by category.

    PivotChart

Worked answers

Try the spreadsheet first, then compare your formula and result.

Build the category PivotTable

From A1:B6 (Category and Amount), what amount should the Hardware row show?

Formula: =SUMIF(A2:A6,"Hardware",B2:B6) → 1800

  1. Select A1:B6, then choose Insert → PivotTable → From Table/Range and place it on a new worksheet.
  2. Drag Category to Rows and Amount to Values; open Value Field Settings and choose Sum.
  3. The result is Hardware 1,800, Software 1,100, Services 500, and Grand Total 3,400.

Verify the PivotTable grand total

What should the category summary grand total be for the five Amount records?

Formula: =SUM(B2:B6) → 3400

  1. In the same PivotTable, click any Sum of Amount value and choose Show Values As → % of Grand Total.
  2. The displayed shares are Hardware 52.94%, Software 32.35%, and Services 14.71%; the Grand Total is 100%.
  3. Switch Show Values As back to No Calculation to restore the 3,400 grand total.

Frequently asked questions

What data shape does a PivotTable need?

Use a flat table with one header row, one field per column, one record per row, and no decorative blank rows or merged headings.

Why is my value field counted instead of summed?

The source may contain text or mixed types in that column. Clean the values, refresh, and select Sum as the aggregation.

Do PivotTables update automatically?

Source edits normally require a refresh, and appended rows must be inside the source range or table.

Continue practicing

Function guides

Learning paths