Learning Excel: Pivot tables with rows and values
Pivot tables
The bank asks how many litres of each beer the brewery sold. Isla could add up twenty rows on a calculator. Or she could build a pivot table in ten seconds.
A pivot table
A pivot table sums up a long list without a single formula. Every column of the list becomes a field.
Put Beer in Rows and you get each beer once. Put Litres in Values and Excel adds up the litres per beer. The Grand Total is at the bottom.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Quarter | Beer | Customer | Packaging | Litres | Price | Revenue | Result | |
| 2 | Q1 | Wise Owl | Crown Inn | Keg | 300 | £2.40 | £720.00 | ||
| 3 | Q1 | Tripel Hoot | Bay Tree Bistro | Bottle | 80 | £4.50 | £360.00 | ||
| 4 | Q1 | Night Owl | Curlew Inn | Keg | 150 | £3.20 | £480.00 | ||
| 5 | Q1 | Wise Owl | Festival Hall | Keg | 600 | £2.20 | £1,320.00 | ||
| 6 | Q1 | Barn Owl | Bay Tree Bistro | Bottle | 60 | £4.00 | £240.00 | ||
| 7 | Q2 | Wise Owl | Crown Inn | Keg | 350 | £2.40 | £840.00 | ||
| 8 | Q2 | Tripel Hoot | Crown Inn | Bottle | 90 | £4.50 | £405.00 | ||
| 9 | Q2 | Night Owl | Bay Tree Bistro | Bottle | 120 | £3.40 | £408.00 | ||
| 10 | Q2 | Wise Owl | Curlew Inn | Keg | 200 | £2.40 | £480.00 | ||
| 11 | Q2 | Barn Owl | Festival Hall | Keg | 250 | £3.60 | £900.00 | ||
| 12 | Q3 | Wise Owl | Festival Hall | Keg | 800 | £2.20 | £1,760.00 | ||
| 13 | Q3 | Tripel Hoot | Bay Tree Bistro | Bottle | 110 | £4.50 | £495.00 | ||
| 14 | Q3 | Barn Owl | Crown Inn | Keg | 180 | £3.80 | £684.00 | ||
| 15 | Q3 | Wise Owl | Curlew Inn | Keg | 250 | £2.40 | £600.00 | ||
| 16 | Q3 | Night Owl | Crown Inn | Keg | 100 | £3.20 | £320.00 | ||
| 17 | Q4 | Night Owl | Festival Hall | Keg | 400 | £3.00 | £1,200.00 | ||
| 18 | Q4 | Tripel Hoot | Curlew Inn | Bottle | 70 | £4.50 | £315.00 | ||
| 19 | Q4 | Wise Owl | Crown Inn | Keg | 320 | £2.40 | £768.00 | ||
| 20 | Q4 | Barn Owl | Bay Tree Bistro | Bottle | 90 | £4.00 | £360.00 | ||
| 21 | Q4 | Tripel Hoot | Festival Hall | Keg | 300 | £4.20 | £1,260.00 | ||
| 22 |
Summarising another way
A number in Values gets added up: Sum. In the list below it you can pick something else: Average, Max, Min, or Count to count the rows.
Put a field with text in Values and Excel counts it: Count of Beer tells you how many orders there are.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Quarter | Beer | Customer | Packaging | Litres | Price | Revenue | Result | |
| 2 | Q1 | Wise Owl | Crown Inn | Keg | 300 | £2.40 | £720.00 | ||
| 3 | Q1 | Tripel Hoot | Bay Tree Bistro | Bottle | 80 | £4.50 | £360.00 | ||
| 4 | Q1 | Night Owl | Curlew Inn | Keg | 150 | £3.20 | £480.00 | ||
| 5 | Q1 | Wise Owl | Festival Hall | Keg | 600 | £2.20 | £1,320.00 | ||
| 6 | Q1 | Barn Owl | Bay Tree Bistro | Bottle | 60 | £4.00 | £240.00 | ||
| 7 | Q2 | Wise Owl | Crown Inn | Keg | 350 | £2.40 | £840.00 | ||
| 8 | Q2 | Tripel Hoot | Crown Inn | Bottle | 90 | £4.50 | £405.00 | ||
| 9 | Q2 | Night Owl | Bay Tree Bistro | Bottle | 120 | £3.40 | £408.00 | ||
| 10 | Q2 | Wise Owl | Curlew Inn | Keg | 200 | £2.40 | £480.00 | ||
| 11 | Q2 | Barn Owl | Festival Hall | Keg | 250 | £3.60 | £900.00 | ||
| 12 | Q3 | Wise Owl | Festival Hall | Keg | 800 | £2.20 | £1,760.00 | ||
| 13 | Q3 | Tripel Hoot | Bay Tree Bistro | Bottle | 110 | £4.50 | £495.00 | ||
| 14 | Q3 | Barn Owl | Crown Inn | Keg | 180 | £3.80 | £684.00 | ||
| 15 | Q3 | Wise Owl | Curlew Inn | Keg | 250 | £2.40 | £600.00 | ||
| 16 | Q3 | Night Owl | Crown Inn | Keg | 100 | £3.20 | £320.00 | ||
| 17 | Q4 | Night Owl | Festival Hall | Keg | 400 | £3.00 | £1,200.00 | ||
| 18 | Q4 | Tripel Hoot | Curlew Inn | Bottle | 70 | £4.50 | £315.00 | ||
| 19 | Q4 | Wise Owl | Crown Inn | Keg | 320 | £2.40 | £768.00 | ||
| 20 | Q4 | Barn Owl | Bay Tree Bistro | Bottle | 90 | £4.00 | £360.00 | ||
| 21 | Q4 | Tripel Hoot | Festival Hall | Keg | 300 | £4.20 | £1,260.00 | ||
| 22 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson