PivotTables
Summarise a sales list by region and month without writing a formula.
Insert a PivotTable, then put the list's headers in Rows, Columns and Values.
This lesson works best on a computer, where the PivotTable has room beside the list.
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Region | Month | Product | Sales | ||||||||
| 2 | East | Jan | Chairs | £1,395.00 | ||||||||
| 3 | West | Jan | Chairs | £660.00 | ||||||||
| 4 | South | Feb | Desks | £535.00 | ||||||||
| 5 | West | Jan | Chairs | £1,340.00 | ||||||||
| 6 | South | Mar | Desks | £330.00 | ||||||||
| 7 | East | Feb | Chairs | £200.00 | ||||||||
| 8 | North | Feb | Desks | £1,575.00 | ||||||||
| 9 | West | Mar | Chairs | £1,785.00 | ||||||||
| 10 | East | Jan | Lamps | £795.00 | ||||||||
| 11 | East | Mar | Chairs | £1,260.00 | ||||||||
| 12 | North | Feb | Lamps | £1,675.00 | ||||||||
| 13 | ||||||||||||
| 14 |
In a bit more detail
What is a PivotTable?
A summary of a list, built by choosing fields instead of writing formulas. Click any cell in the list, then Insert → PivotTable. Each header of the list becomes a field.
Where do the fields go?
Rows: one line per value (each region). Columns: one column per value (each month). Values: the numbers to add up in each box. Drag a field to an area, or tick it and Excel picks one.
Why does it say Sum of Sales?
Sales are numbers, so Excel adds them up. A text field dropped in Values is counted instead (Count of Product). To change it, use Value Field Settings: Sum, Count, Average, Max, Min.
What are the Grand Totals?
The total of each row and each column, added for you. Months go across in calendar order, not A to Z: Excel knows month names.
Why is it called a pivot?
You can turn it round: swap Rows and Columns and the same numbers are shown the other way. Nothing in the list changes.
Does it update by itself?
No. When the list changes, right-click the PivotTable and choose Refresh (Alt+F5). If the list is an Excel table, new rows are included when you refresh.
How do I do it with formulas?
=SUMIFS(D:D, A:A, "North", B:B, "Jan")gives one box of the PivotTable. The PivotTable is quicker to build; formulas update by themselves.
Know someone fighting with a spreadsheet? Send them this page.