Your first PivotTable
Insert a PivotTable, total sales by region in two clicks, then switch it to products. Intermediate · 5 min · PivotTable, Rows, Values, Sum
A PivotTable summarises a list without a single formula. You choose a field to group by and a field to total, and it builds the table for you, with a grand total.
Here are 24 orders. Each row is one sale, and the first row holds the headings, which become the PivotTable's fields.
- Rows: what to group by, one row per region, product or rep.
- Values: what to add up for each group, such as the Sales.
In Excel, it's Insert → PivotTable, then OK. The PivotTable Fields pane opens on the right, and it works the same way here. On a phone, tap Fields in the toolbar to open it, and use Add to… next to a field instead of dragging.
One difference from Excel: here the PivotTable updates the moment the data changes. In Excel it doesn't: after editing the data, click inside the PivotTable and choose PivotTable Analyze → Refresh (or press Alt+F5).
Steps
Click any cell in the orders, then PivotTable in the toolbar. The data (
A1:F25) and the place for the PivotTable (H1) are filled in for you: click Create.In the PivotTable Fields panel, tick Region. Text fields go to Rows, so you get one row per region.
Now tick Sales. Numbers go to Values, where they are added up: Sum of Sales for each region, and a Grand Total.
Same question, different angle: which product sells best? Drag Product from the field list onto the Rows area. It replaces Region.
Remember
- Insert → PivotTable on any cell of a list with headings. Each heading becomes a field.
- Fields in Rows group the data; fields in Values are added up for each group, with a Grand Total.
- Ticking a field puts text in Rows and numbers in Values. Drag fields to move them.
- One difference from Excel: here the PivotTable updates the moment the data changes. In Excel it doesn't: after editing the data, click inside the PivotTable and choose PivotTable Analyze → Refresh (or press Alt+F5).
Previous: Moving averages: smoothing out the noise · Next: PivotTables: count, average and more values