From a long list to a small summary
A sales list has hundreds of rows. The manager wants one small grid: a row for each shop, a column for each month, and the units in the boxes.
A pivot table makes that grid from the list in a few steps.
Key words
| Word | What it means |
|---|---|
| Pivot table | a summary grid made by regrouping a list |
| Row field | the column whose values become the rows |
| Column field | the column whose values become the columns |
| Value field | the column that is added up in the boxes |
| Cell range | a block of cells, such as A1 to D7 |
| Refresh | recalculate the pivot table from the source list |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can design a pivot table from a need and test that it adds up.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Organise the layout and input the data
A pivot table needs a tidy list. Put column names in the first row. Give every row a value in every column. No blank rows, no merged cells, one kind of value per column.
Then type or paste the data in.
The source list as a grid of cells
| A | B | C | D | |
|---|---|---|---|---|
| 1 | shop | month | product | units |
| 2 | Leeds | Jan | helmet | 12 |
| 3 | Leeds | Jan | lock | 30 |
| 4 | Leeds | Feb | helmet | 9 |
| 5 | York | Jan | helmet | 7 |
| 6 | York | Feb | lock | 18 |
| 7 | York | Feb | helmet | 11 |
The cells students fill in are left blank in this preview.
Check the layout
Have a go
Check the layout
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.