Learn data analyticsModule 2 of 4 · 0 done
Spreadsheets & cleaning data
Tidy data, lookups, pivots and the cleaning steps that prevent silent errors.
By the end you’ll be able to
- Recognise tidy data
- Clean duplicates, types and missing values
- Summarise with pivot tables and lookups
Tidy data
One row per observation, one column per variable, one table per type of thing. Most cleaning is reshaping data into this form.
The cleaning checklist
Remove exact duplicates, fix types (dates stored as text), standardise categories (“UK”, “U.K.”, “United Kingdom”), decide how to treat missing values, and check ranges for impossible values.
- Never overwrite raw data; clean in a copy
- Document every step
- Check totals before and after
Summarise fast
Pivot tables answer “by X, how much Y?” in seconds. XLOOKUP/VLOOKUP joins tables. Conditional formatting spots outliers.
Clean a messy dataset
Use a messy public dataset (or export your own bank statement).
Deliverable: A cleaned sheet, a cleaning log and two pivots. Add it to your skills passport as a project when it’s done.
Check your understanding
Answer 3 scenario questions. Get 2 or more right to complete the module.
See how your spreadsheets & cleaning scores
A free test adds Spreadsheets & cleaning to your skill report and shows how close you are to the 75% job-ready target.