Free account

Save your progress

Keep your skill report, learning progress and passport safe, and pick up on any device.

  • Report updates after every test
  • Learning progress on every device
  • Reserve your passport link
or with email
Free forever. No card. Your scores stay private unless you choose to share them.
Learn data analyticsModule 2 of 4 · 0 done
  1. Asking good questions of data
  2. Spreadsheets & cleaning data
  3. SQL & statistical thinking
  4. Visualisation & data storytelling
Module 2 of 4 · Spreadsheets & cleaning · 30 min

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.

Practical exercise

Clean a messy dataset

Use a messy public dataset (or export your own bank statement).

0/5 steps

Deliverable: A cleaned sheet, a cleaning log and two pivots. Add it to your skills passport as a project when it’s done.

Checkpoint

Check your understanding

Answer 3 scenario questions. Get 2 or more right to complete the module.

1. In tidy data, each row is…
2. Dates are left-aligned and won’t sort correctly. Likely cause?
3. Best practice with raw data?
Measure it

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.

Go deeper

Free PDF · 74 pages · 2 editions

The Laws of UX, in one field guide

60 research-backed principles in 6 categories — what each one means, why it works on the human brain and how to use it on your own screens. Written by Sushant Singh, founder of Science of Apps, who has scaled consumer apps to lacs of users and spent 10+ years leading product across startups and digital businesses in the US, EU & India.

Pick your edition
Free PDF · 74 pages

Get Laws of UX by email

Choose your edition

You’ll also get occasional UX psychology tips. Unsubscribe anytime. Privacy policy

Free PDF · 90 pages · 2 editions

100+ Subscription Paywalls, decoded

110 real paywalls from top apps, taken apart, rated out of 5 and decoded with the Laws of UX — plus the frameworks, copy and checklists to ship your own. By Sushant Singh, founder of Science of Apps.

  • 110 paywalls rated
  • 34 Laws of UX
  • 12 frameworks

Which paywall scored 4.8 out of 5, and which scored 2.6? It’s all inside.

Pick your edition
Free PDF · 90 pages

Get 100+ Paywalls, Decoded by email

Choose your edition

You’ll also get occasional UX psychology tips. Unsubscribe anytime. Privacy policy