Quick answer: A PivotTable summarizes thousands of rows in four clicks: select your data → Insert → PivotTable → drag a text field into Rows, a numeric field into Values (it defaults to Sum), and optionally a field into Columns or Filters. That is the entire core skill — everything else is arrangement. This guide walks a real example, then the four customizations everyone needs in week one. Prerequisite comfort with formulas: our essential formulas guide.
Worked example: sales by region
Imagine 5,000 rows: Date, Region, Product, Units, Revenue. The questions a manager asks — “what did each region sell?”, “which product leads?” — are all PivotTable questions:
- Rows: Region → each region becomes a row
- Values: Revenue → Sum of Revenue per region
- Columns: Product → a column per product inside each region
- Filters: Date → slice to a month without rebuilding
Two setup rules prevent 90% of pivot pain: your data needs one header row with unique names and no merged cells or blank rows — and the best source is an Excel Table (Ctrl+T), because pivots on Tables auto-expand when new rows arrive.
The four customizations for week one
| Need | How |
|---|---|
| Count instead of sum | Value Field Settings → Summarize by Count (text fields default to Count) |
| Percent of total | Value Field Settings → Show Values As → % of Grand Total |
| Group dates by month | Right-click a date → Group → Months (and Years) |
| Sort by value not alphabet | Right-click a value → Sort → Largest to Smallest |
Refresh, and the classic gotcha
Pivots are snapshots: edit the source data and the pivot does not change until you refresh (Right-click → Refresh, or Data → Refresh All, Alt+F5). The classic gotcha — “my new rows aren’t showing” — is a plain-range source; converting the source to a Table (Ctrl+T) makes new rows appear on every refresh automatically. When a summary needs explaining to an audience, add a PivotChart (Insert → PivotChart) that stays linked to the same fields.
For guided practice on real datasets, Ampersand Academy teaches Advanced Excel one-to-one, from pivots to dashboards.
Frequently asked questions
What is a PivotTable used for?
A PivotTable quickly summarizes large datasets: totals, counts, averages and percentages grouped by any combination of fields, rearrangeable by dragging. It answers summary questions without writing formulas.
Why is my PivotTable not updating with new data?
Pivots do not auto-refresh, and plain-range sources do not grow. Refresh manually, and convert your source to an Excel Table (Ctrl+T) so new rows are included automatically on each refresh.
How do I group dates by month in a PivotTable?
Right-click any date in the pivot rows, choose Group, select Months and Years. Excel then creates month and year fields you can filter and arrange like any other.
Can a PivotTable show percentages instead of totals?
Yes. In Value Field Settings, open Show Values As and choose % of Grand Total, % of Row Total or % of Column Total – the same field can be shown twice, once as a value and once as a percentage.
Do PivotTables work in Excel for Mac and Excel Online?
Yes. Both support creating and refreshing PivotTables; Excel Online has a slightly reduced field list, while desktop – Windows or Mac – has the full feature set.