Cleaning Messy Data Into Something Usable

⚠️ Version note. Menu locations differ across Excel, Google Sheets and versions. The cleaning steps are universal. Last checked: 25 July 2026.

What you'll have at the end

• The definition of a tidy table (the target every clean-up aims at)
• One real messy dataset cleaned: trimmed, de-duplicated, split, typed, and gap-checked
• A repeatable cleaning checklist you run on any new data

Prerequisite

• TL-01 — Spreadsheets From Zero. You need basic formulas and cell references.
• A real, messy dataset (an export from an app, a copied web table, a survey download).

"Garbage in, garbage out" is a spreadsheet law

Every analysis (TL-02, AI-09, the charts in PS-04) assumes clean input. Feed it messy data and you get confident nonsense. Cleaning isn't glamorous, but it's where most real spreadsheet time goes and it's the difference between a chart that's true and one that's a lie you made by accident.

The target is a tidy table: one row per record, one column per variable, one value per cell, and consistent types. Everything below moves your data toward that.

Never clean the original. Duplicate the sheet, work on the copy, so you can always go back.

Then look: scroll the whole thing. Note the problems — trailing spaces, blank rows, two things crammed in one column, dates stored as text, duplicates.

Write down each specific mess you spot — a written list is what turns "this looks messy" into a checklist you can actually work through.

✅ Check: you have a working copy and a written list of the specific messes in it.

The most common, most invisible problem: stray spaces that stop values matching ("Apple " ≠ "Apple").

• Use TRIM() to remove leading/trailing and doubled spaces; CLEAN() to strip non-printing characters.
• Apply in a helper column, then paste the result back as values.

✅ Check: a value that looked identical but wouldn't match now matches after trimming.

If it moved: TRIM/CLEAN exist in both Excel and Sheets. If a lookup still fails after trimming, the culprit is often a hidden character or a number-stored-as-text (Step 4).

Duplicates inflate every count and total.

• Excel: Data → Remove Duplicates. Sheets: Data → Data cleanup → Remove duplicates.
• Decide first which columns define a duplicate (a whole-row match, or just an ID/email).

The dialog asks which columns define a duplicate — a whole-row match, or just an ID/email. Choose before you confirm; "remove duplicates" on the wrong key can delete rows that were never repeats.

✅ Check: the row count dropped by the number of duplicates, and the ones removed were genuinely repeats.

Two problems, one idea — one value per cell, correct type:

• Split "Firstname Lastname" or "City, Country" into separate columns with Text-to-Columns (Excel) / Split text to columns (Sheets).
• Fix types: numbers stored as text won't sum; dates stored as text won't sort. Convert them (multiply-by-1 trick for numbers, DATEVALUE/format for dates), and watch for the little green-triangle "number stored as text" warning in Excel.

✅ Check: each column now holds one variable, and numbers add up / dates sort correctly.

If it moved: if a "number" column won't SUM, it's text. Select it, and use the error-flag "Convert to Number", or =VALUE(cell) in a helper column.

• Blanks: decide per column — is a blank a real zero, a genuine "unknown", or a row to drop? Don't silently fill; a wrong fill corrupts the analysis (DT-01).
• Inconsistent categories: "UK", "U.K.", "United Kingdom" are one thing typed three ways. Standardise them (find-and-replace, or a lookup to a clean list) so grouping works.

✅ Check: each column's blanks are handled deliberately, and categories are spelled one way.

Before you trust it, sanity-check:

• Row count — does it match what you expect?
• A total or average — is it in a plausible range?
• Sort each key column — do the extremes look real (no stray "999999" or a date in 1900)?

✅ Check: counts and totals are plausible, and no obvious junk survives at the extremes.

Common mistakes

• Cleaning the original. Always work on a copy.
• Skipping TRIM. Invisible spaces cause silent match failures for hours.
• Filling blanks with zero by reflex. A blank isn't always a zero; a wrong fill skews everything.
• Leaving numbers as text. They won't sum or sort, and you won't notice until the total is wrong.
• Not standardising categories. "UK/U.K./United Kingdom" splits one group into three in every pivot.
• Not verifying at the end. Clean-looking isn't clean; check counts and extremes.

Exercise (30 min)

1. Get a real messy dataset and make a working copy (Step 1).
2. Run the cleaning checklist: trim, de-duplicate, split combined fields, fix types, handle blanks and categories (Steps 2–5).
3. Verify with a row count and one total (Step 6).
4. Note how many rows you removed and which columns you fixed.

✅ Finish check: you have one tidy table — one row per record, one value per cell, consistent types — plus a note of what you removed and fixed.

Cheatsheet

Next lesson: TL-04 — Spreadsheets, Level Three: Dynamic Arrays, Power Query, Dashboards (L3) Related: TL-01 Spreadsheets From Zero (L1) · TL-02 Spreadsheets Level Two (L2) · AI-09 Working With Data (L2) Path: Spreadsheet Fluent — 3/5