Free preview lesson
Know your mess
What messy data looks like
Messy data means information in a spreadsheet that looks fine to a person but confuses formulas, sorting and charts. It usually comes from copying and pasting, exports from other apps, or several people typing in their own way.
Common problems:
- Extra spaces: "Coffee " with a space at the end doesn't match "Coffee"
- Mixed capitals: "north", "North" and "NORTH" look like three regions to some tools
- Numbers stored as text: they look like numbers but won't add up
- Mixed date styles: 3/4/2026, 2026-03-04 and "March 4" in one column
- Duplicates: the same customer or order listed twice
- Blanks where something should be
You can spot many of these quickly. Sort a column and look for near-matches next to each other. Or count with a formula and compare:
=COUNTIF(B2:B200,"North")
If that number seems too low, some rows probably say "north " or "Nrth".
Weak: "my data is messy, fix it"
Better: "Google Sheets. Column B is Region, rows 2 to 200. Some entries have extra spaces and mixed capitals. Give me a formula for a new column that cleans them so I can count them correctly."
The most important rule: always clean a copy, never your only file. Cleanup changes a lot of cells at once.