1 - Introduction ~4 mins
Welcome
Real survey answers are rarely tidy. Today you clean a working copy of {{code:01_survey_responses}} and log every change. If that file is missing or has fewer than 10 answer rows, use the messy sample in this lesson. That is fine.
By the end of this lesson you will:
- Make a working copy named {{code:02_survey_analysis}}.
- Freeze the header and add a table (Excel) or filter (Sheets).
- Clean Transport with TRIM and PROPER, then paste as values.
- Remove duplicates, fix impossible answers, and log every change.
Warm-up
If one journey is recorded as 250 minutes, what goes wrong when you average travel time? Share when asked, then continue.
2 - Key Concepts ~4 mins
Four ideas keep messy answers usable and honest.
| Concept | Why it matters | Example |
|---|---|---|
| Freeze header: row 1 stays visible while you scroll | You still see column names when you filter lower rows | Scroll to response 24: headers still read Transport, Minutes |
| Table or filter: header filters on the full block | Clean-up tools hit every row, not a half-selected column | Filter Transport to Bus for school-bus checks |
| TRIM and PROPER: strip extra spaces; standardise capitals | " bus", "BUS" and "Bus" must become one spelling | - |
| Cleaning log: records each change and why | Readers see what you fixed and what you left alone | Removed duplicate response 9: would have double-counted Bus |
3 - Step-by-step a: Structure and Clean Transport ~14 mins
Paste the messy sample, freeze the header, add filters, then clean Transport only with TRIM and PROPER. Stuck? Jump to Common Issues, then return. If a formula stays in Transport, undo and Paste Values again.
Live demo today = Transport only. Class and Would_cycle wait for the portfolio stretch with different formulas (see the column map after you delete the helper).
Messy sample data (copy the whole table, then paste at {{code: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 |
| 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 | 250 | 10 | Maybe | 3 |
| 15 | TY2 | Car | 25 | 0 | No | 3 |
| 16 | TY2 | Walk | 5 | 0 | No | |
| 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 |
Spot later: mixed Transport text; response 9 twice; 250 minutes; cost -12; blank rating on 16 (leave blank).
4 - Step-by-step B: Duplicates, Impossibles, Log ~10 mins
Still in {{code:practice_cleaning}}, remove the duplicate response 9, fix the two impossible numbers, then build {{code:cleaning_log}} and rename the data sheet {{code:clean_data}}. Stuck? Jump to Common Issues, then return.
Practice rules: delete the whole row when Minutes is 250; clear only the cell when Cost_per_week is -12; leave blank Journey_rating blank.
If the sheet looks empty after a filter, you probably still have only 250 or -12 ticked. Select all / Clear filter and the rows come back.
5 - Common Issues ~2 mins
Common Issues
| Issue | Solution |
|---|---|
| No {{code:Digital_Spreadsheets}} folder | Create it in OneDrive or Drive, then continue. |
| Transport still shows a formula | Undo, copy the helper, Paste Values (Excel) or Values only (Sheets). |
| Paste Values hard to find | Home Paste arrow → Paste Values (clipboard with 123). |
| Fill handle square missing | Click the formula cell first, then use the bottom-right square. |
| Remove Duplicates broke rows | Click inside the full block so every column is included. |
| No Convert to table in Sheets | Use {{menu:Data -> Create a filter}} instead. |
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.