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.
One answer in a travel survey says a journey to school took 250 minutes. If you leave it in when you work out the average journey time, what goes wrong? Be ready to share your idea.
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 | {{formula:=PROPER(TRIM(C2))}} turns " bus" and "BUS" into Bus |
| 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 |
Paste the messy sample into a new workbook, freeze the header row and add filters. Then clean the Transport column with TRIM and PROPER. If you get stuck, look at the Common Issues table in the next step, then come back. If a formula stays in Transport, undo and paste again with Values only.
Today you clean the Transport column only. Class and Would_cycle need different formulas. They are an extra challenge in the Portfolio Build later in this lesson.
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 |
Problems you will fix in this lesson: Transport is spelled in different ways, response 9 appears twice, one journey says 250 minutes, one weekly cost is -12, and response 16 has no journey rating (leave that one blank).
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}}. If you get stuck, look at the Common Issues table in the next step, then come back.
Practice rules: delete the whole row when Minutes is 250; clear only the cell when Cost_per_week is -12, so response 21 keeps its other answers; leave the blank Journey_rating for response 16 blank.
If the sheet looks empty after a filter, the filter is still showing only 250 or -12. Open that column's filter, select all the values again, then click Apply (Excel) or OK (Google Sheets), and the rows come back.
Log rows to copy (click {{cell:A2}} on {{code:cleaning_log}}, then paste):
| 1 | Transport column | Mixed capitals and spaces | PROPER and TRIM, paste as values | So Bus counts as one group |
| 2 | Response 9 | Same row submitted twice | Removed duplicate row | Two identical answers would double-count |
| 3 | Minutes | Value 250 | Deleted whole row | Impossible school journey time |
| 4 | Cost_per_week | Value -12 | Cleared cell only | Cost cannot be negative |
| 5 | Journey_rating response 16 | Blank cell | Left blank | Do not invent missing answers |
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 123Learn.ie 2017 - 2026. Unauthorised use, copying or distribution is not allowed.