Learning Excel: Columns, filters and % of Grand Total
Columns, filters and percentages
The bank is happy, and now the sales rep wants more: which beer sells in which quarter, and what share of the revenue comes from which customer? The same pivot table can do it, with three more buttons.
Columns
Put a field in Columns and the pivot table becomes a cross-tab. With Beer in Rows and Quarter in Columns you get a row for each beer and a column for each quarter.
On the right is the Grand Total of each row, at the bottom that of each column.
| 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 |
Filters
A field in Filters doesn't appear in the table itself, but picks which rows of the list count. At the top you choose a value, or (All).
That way you make the same pivot table for just the kegs, or for just one customer.
| 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 |
Percent of the Grand Total
For a field in Values you can also choose how its numbers are shown. With % of Grand Total, each number becomes a share of the whole.
The Grand Total is then always 100%. You can see at once who has the biggest slice of the pie.
| 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 4 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson