Today you will
- learn the rules for tidy data
- clean up a messy stock list
- clean your own project spreadsheet
You need {{code:sm3_from_template}} from {{code:Project_Portfolio/09_specialism/sm3}}. If you don't have it, make a new spreadsheet with that name in {{code:sm3}} and copy this table into cell {{cell:A1}}.
| Item | Unit cost (€) | Quantity |
|---|---|---|
| Keyrings | 2.50 | 20 |
| Tote bags | 6.00 | 10 |
| Badge packs | about 3 | 15 |
| Notebooks | 4.00 | 12 |
| Pens (pack of 5) | 5 euro | 8 |
Warm-up
Imagine some prices in a list say "about €12". What would go wrong when you tried to total them?
Four rules for tidy data
Headings in row 1: the top row names each column, and the data starts on row 2.
One type per column: numbers stay numbers, dates stay dates and text stays text.
No blank rows: empty rows in the middle of a table make sort and filter stop halfway.
No merged cells: merged cells inside a table break sorting, filtering and formulas.
Example
Type 12.5 in a cost cell and format it as euro. Don't type "€12.50" or "about 12".
You will type a messy stock list for a phone-case side hustle, and then clean it up. Follow the steps in the box below, and keep pace with your teacher.
Type these rows exactly as they are, mistakes and all.
| Row | A | B | C | D |
|---|---|---|---|---|
| 5 | Clear case | €8.50 | 12 | School colours |
| 6 | Matte black case | 10 | 7 | |
| 7 | (leave this whole row empty) | |||
| 8 | Sticker pack | about 3 | 20 | GAA crest |
| 9 | Charging cable | €4 | 5 | USB-C |
Common issues
- My unmerged text sits in one cell. That is normal, so delete it and put your headings on row 1.
- My costs still won't add up. Click a cost cell and look for a euro sign or words, then retype a plain number.
- My headings jumped after a delete. Press {{kbd:Ctrl+Z}} or {{kbd:Cmd+Z}} to undo, then check the row numbers again.
Independent Practice
- Open {{code:sm3_from_template}} in {{code:09_specialism/sm3}}.
- Check it. Are the headings on row 1? Are there blank rows or merged cells in the table? Does each column hold one kind of value?
- Fix anything that fails. Move headings to row 1, delete blank rows, unmerge cells and retype text prices as numbers.
- Keep at least five rows of your own project data.
- Save a copy as {{code:sm3_data_input_clean}} in the same {{code:sm3}} folder.
- {{code:sm3_data_input_clean}} is saved in {{code:sm3}}.
- My headings are on row 1.
- My table has no blank rows and no merged cells.
- Each column holds one kind of value, with five rows of my own data.
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 - 2026. Unauthorised use, copying or distribution is not allowed.