Summarising with Pivot Tables
A pivot table turns a long list of rows into a tidy summary — totals by category — without a single formula.
What you will learn
- Explain what a pivot table is for
- Build a pivot that totals sales by category
- Drag fields into rows and values
The most powerful analyst tool
A pivot table summarises a big list automatically. Give it hundreds of rows and it answers questions like total sales per region or how many orders per product in seconds — and you write no formulas at all. You just drag fields around.
The raw data
Pivot tables start from a plain table where each row is one record and the top row holds the headings. Here is some order data:
| Region | Product | Sales |
|---|---|---|
| North | Apples | 120 |
| South | Apples | 80 |
| North | Bananas | 90 |
| South | Bananas | 60 |
| North | Apples | 100 |
Building the pivot
Click any cell in the data, then choose Insert > PivotTable and press OK. A panel appears with your field names. To total sales by region, you drag two fields:
- Drag Region into the Rows box (this lists each region down the side).
- Drag Sales into the Values box (this adds up the sales for each region).
Rows: Region
Values: Sum of SalesNote: Output: North 310 South 140 Grand Total 450 The pivot grouped all the North rows (120 + 90 + 100 = 310) and all the South rows (80 + 60 = 140), then totalled everything to 450. No formulas — just two drags.
Pivots can slice many ways
The magic word is pivot — you can rearrange instantly. Drag Product into Rows instead and you get totals per product. Drag Region into Rows and Product into Columns and you get a grid of region-by-product. Same data, a new view in seconds.
| Drag into Rows | You get |
|---|---|
| Region | Total sales for each region |
| Product | Total sales for each product |
| Region, then Product | Each region broken down by product |
Count instead of total
By default the Values box adds up (Sum) your numbers. But sometimes you want to know how many orders there were, not their total. Click the field in the Values box, choose Summarize Values By > Count, and the same pivot answers a new question:
Rows: Region
Values: Count of SalesNote: Output: North 3 South 2 Grand Total 5 Now each row shows the number of orders, not the money: North had 3 orders and South had 2. Same data, same drag — only the Values setting changed from Sum to Count.
Tip: The Values box also offers Average, Max and Min. So one pivot can tell you the total sales, the order count, and the average order value for each region — just by changing how the value is summarised.
Watch out: If you add new rows to your source data, the pivot does not update by itself. Right-click the pivot and choose Refresh to pull in the new rows.
Q. To total sales for each region in a pivot table, where do you drag the fields?
✍️ Practice
- Recreate the five-row order table above and build a pivot that totals Sales by Region.
- Switch the Rows field to Product to see totals per product instead.
🏠 Homework
- Make a 12-row sales table with Region, Product and Sales. Build a pivot of total sales per region, then change the Values setting to Count to see the number of orders per region.