1 - Introduction ~5 mins
Welcome
Today you build a results explorer: pick one option and three figures update at once.
By the end you will:
- Build a drop-down from summary options
- Fetch count, average and percentage with XLOOKUP
- Keep empty cells tidy with IFERROR
Warm-up
Would a busy principal rather see a long table or one box they can change?
Ready: open your cloud storage, open {{code:Digital_Spreadsheets}}, then open {{code:02_survey_analysis}} (or create a new workbook with that exact name). No finished summary yet? Use the on-screen sample in the next task.
2 - Key Concepts ~5 mins
| Concept | Why it matters | Example |
|---|---|---|
| Drop-down list: limits a cell to a set list | Stops typing errors on options | Only Bus, Walk, Car, Cycle or Train |
| XLOOKUP: finds a value and returns a match | One formula pulls the right figure | Pick Bus; count shows 8 |
| IFERROR: your text instead of an error | Empty pick stays tidy | Shows Pick an option |
| Results explorer: drop-down plus live figures | Reader tests options fast | - |
Formula shape: {{code:XLOOKUP(what_to_find, where_to_look, what_to_return)}} then {{code:IFERROR(that_formula, "Pick an option")}}.
3 - Step-by-step Task: Build a Results Explorer ~17 mins
Build a practice explorer on sample transport figures. Paste the summary, add a drop-down, learn plain XLOOKUP, wrap it in IFERROR, then finish the other two figures.
Sample summary (paste at A10; headers in row 10):
| Transport | Count | Average minutes | Percentage |
|---|---|---|---|
| Bus | 8 | 31.3 | 0.333 |
| Walk | 5 | 11.6 | 0.208 |
| Car | 6 | 18.7 | 0.25 |
| Cycle | 3 | 15 | 0.125 |
| Train | 2 | 42.5 | 0.083 |
If paste breaks columns, Undo and type the six rows by hand.
4 - Common Issues ~3 mins
Common Issues
| Issue | Solution |
|---|---|
| Drop-down empty or wrong list | Edit validation; list = options only, no header |
| {{code:#N/A}} on a valid option | Match search range to drop-down list and $ locks |
| Wrong figure for a valid pick | Return letter: $B count, $C average, $D share |
| Share shows 0.33 not 33% | Apply Percentage format to that cell |
5 - Portfolio Build: 06_explorer ~20 mins
Independent Practice
Sheet setup. Open {{code:02_survey_analysis}}. Add a sheet named {{code:06_explorer}}. Layout: title in A1; labels in A3:A6 (Transport, Count, Average minutes, Share of answers); drop-down in B3; figures in B4:B6.
Data source. Path A (you have {{code:03_summary}}): click the header row and note the column letters for options, count, average and percentage. Point validation Source and each XLOOKUP at those ranges. Example: options A2:A6, counts B2:B6, averages C2:C6, percentages D2:D6 become search {{code:$A$2:$A$6}} and returns {{code:$B$2:$B$6}} / {{code:$C$2:$C$6}} / {{code:$D$2:$D$6}}. Path B (no summary yet): paste the walkthrough sample at A10 and use {{range:A11:A15}}. Personalise Path B by renaming A1 to your survey topic (required).
Build checklist:
- Data validation on B3 equals your options range
- Three IFERROR(XLOOKUP) formulas in B4:B6 for count, average and percentage
- Format share as percentage
- Test two picks, then clear B3
Own data or a personalised sample counts as complete. Finish for homework if needed.
- Sheet {{code:06_explorer}} sits in {{code:02_survey_analysis}}
- One drop-down offers answer options from a summary list
- At least three figures update correctly when you change the pick
- Clearing the drop-down shows a clean message, not an error code
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.