1 - Introduction ~5 mins
With 24 answers, hand-counting transport by class is slow. What if you had 240?
Today you build a pivot grid, show percentages, and spot-check one total with COUNTIF.
By the end of this lesson, you will be able to:
- Build a pivot with rows and columns
- Show counts and % of grand total, and try a filter
- Match one pivot total to a COUNTIF
- Note the most interesting number on {{code:07_pivot}}
Open {{code:02_survey_analysis}}. The guided build runs on a sample you paste in the next activity.
2 - Key Concepts ~5 mins
| Concept | Why it matters | Example |
|---|---|---|
| Pivot table: summary grid from clean data | Two questions cross-tabbed in seconds | Transport × Class counts |
| Row and column fields: the grid sides | Swapping fields changes the story | Rows: Transport. Columns: Class |
| % of grand total: each count as a share of all | Fairer when groups differ in size | 8 bus of 24 ≈ 33% |
| Cross-check: match one pivot cell to COUNTIF | Proves the pivot read the data right | Bus total = COUNTIF of Bus |
3 - Step-by-step Task: Build a Practice Pivot ~20 mins
Guided build uses the on-screen sample only. You will make a Transport × Class pivot with counts and % of grand total, try a filter, and prove one total with COUNTIF. Save it as {{code:07_pivot_practice}}. The portfolio sheet, {{code:07_pivot}}, comes later.
If the first grid looks empty or wrong, delete that sheet and recreate it. That is a two-minute fix, not a failure.
Sample data (add a sheet named {{code:sample_data}} and paste this at {{cell:A1}}):
Response Class Transport Minutes Cost_per_week Would_cycle Journey_rating 1 TY1 Bus 25 12 Maybe 3 2 TY1 Walk 10 0 No 4 3 TY1 Car 15 0 Yes 3 4 TY1 Bus 40 15 No 2 5 TY1 Cycle 12 0 Yes 5 6 TY1 Walk 8 0 No 5 7 TY1 Train 45 25 No 2 8 TY1 Car 20 0 Maybe 3 9 TY2 Bus 30 12 Yes 2 10 TY2 Walk 15 0 Maybe 4 11 TY2 Bus 35 15 No 2 12 TY2 Car 10 0 Yes 4 13 TY2 Cycle 18 0 Yes 4 14 TY2 Bus 20 10 Maybe 3 15 TY2 Car 25 0 No 3 16 TY2 Walk 5 0 No 5 17 TY3 Bus 50 15 No 1 18 TY3 Train 40 20 Maybe 2 19 TY3 Car 12 0 Yes 4 20 TY3 Walk 20 0 Maybe 3 21 TY3 Bus 28 12 Yes 3 22 TY3 Cycle 15 0 Yes 5 23 TY3 Car 30 0 Maybe 2 24 TY3 Bus 22 10 No 3
4 - Common Issues ~3 mins
Common Issues
| Issue | Solution |
|---|---|
| Empty pivot or one huge total | Recreate so the range includes every header and data row |
| Sum of Response or inflated totals | Set Count (Excel Value Field Settings) or COUNTA (Sheets). Never sum IDs |
| Percentages show as 0.33 not 33% | Format as Percentage. Open the second Values field and set Show Values As / Show as to % of Grand Total |
| COUNTIF does not match the pivot | Match spelling, use the real Transport column, and clear any filter first |
5 - Portfolio Build: 07_pivot ~17 mins
Independent Practice
- Sheet {{code:07_pivot}} compares two real survey questions (not Response ID)
- Values show counts and % of grand total
- COUNTIF matches a grand total; filter cleared; a note names the group you filtered
- A short note names the most interesting number and why it matters
Unlock the full learning experience
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.