Course / PivotTables

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.

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

  1. 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.

  2. In the PivotTable Fields panel, tick Region. Text fields go to Rows, so you get one row per region.

  3. Now tick Sales. Numbers go to Values, where they are added up: Sum of Sales for each region, and a Grand Total.

  4. Same question, different angle: which product sells best? Drag Product from the field list onto the Rows area. It replaces Region.

Remember

Previous: Moving averages: smoothing out the noise · Next: PivotTables: count, average and more values