# PivotTables (Excel lesson 10 of 45)

> Summarize hundreds of rows by any field in a few clicks, with no formulas to write.

Source: https://spreadsheetpractice.com/learn/pivottables · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Data tools"
- **Time:** about 12 minutes, 4 graded exercises
- **Functions:** `PivotTable`, `Rows`, `Values`, `Filters`
- **Access:** free
- **Key formula:** `Rows: Region · Values: Sum of Revenue`: Total revenue per region

## How it works

Click any cell in your data, then Insert > PivotTable and choose where to put it. Excel reads the whole table, header row included, and opens the **PivotTable Fields** pane. Each column header becomes a field you can drop into one of four areas.

**Rows** makes one row per value: Region in Rows gives one row per region. **Values** is what gets calculated: numbers are summed by default, and text is counted. **Columns** splits each row across the values of a second field. **Filters** keeps only the rows you pick.

To change the math, open Value Field Settings. **Summarize Values By** switches between Sum, Count, Average, Max and Min. **Show Values As > % of Grand Total** turns each number into its share of the whole. To put the biggest first, click a value and choose Sort Largest to Smallest.

The practice editor works like the Fields pane: add a field to an area, then use its settings. A PivotTable doesn't update on its own when the data changes. Right-click it and choose Refresh.

## Syntax

| Formula | What it does |
|---|---|
| `Rows: Region · Values: Sum of Revenue` | Total revenue per region |
| `Values: Count of Order` | How many orders |
| `Rows: Rep · Columns: Region` | A grid of reps by region |
| `Show Values As: % of Grand Total` | Each row's share of the total |
| `Filters: Region = East` | Only the East rows |

## Watch out for

- A PivotTable doesn't recalculate by itself. After the data changes, right-click it and choose Refresh.
- Blank cells or text in a number column make Excel count instead of sum. Check the label: "Count of Revenue" means something in the column isn't a number.
- Headers matter: every column needs a unique name in row 1, or Excel can't build the field list.
- Sorting the field A to Z sorts the names. To rank by the numbers, sort by the value.

## The exercises

Solve them in the live grid at [PivotTables](https://spreadsheetpractice.com/learn/pivottables). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Revenue by region.** Build a PivotTable from A1:F21 that shows total revenue for each region: Region in Rows and Revenue in Values, summed. Keep the grand total.
2. **Most orders first.** Count the orders each rep took: Rep in Rows and Order in Values, summarized by Count. Sort the reps so the one with the most orders comes first.
3. **Reps by region.** Show revenue for every rep in every region: Rep in Rows, Region in Columns, and Revenue in Values (Sum). Keep the grand totals.
4. **East product mix.** For the East region only, show each product's share of revenue: Product in Rows, Revenue in Values shown as % of Grand Total, and a Region filter set to East.

Previous: [Conditional formatting with formulas](https://spreadsheetpractice.com/learn/conditional-formatting-formulas) · Next: [Charts: pick the right one](https://spreadsheetpractice.com/learn/charts)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
