Clean up messy data
- spreadsheets
- data cleanup
Ask anyone who analyses data for a living where the work goes and you will hear the same answer: most of it disappears into cleaning. Dates arrive in three formats, one column carries two decimal separators, blanks mean “missing” in some rows and “zero” in others, and someone double-entered 38 orders. Analysis on top of that mess produces numbers nobody should trust.
Cleaning is a workflow, not a prompt
The useful mental shift: AI cannot see your file, so it cannot clean it for you. What it can do — well — is help you plan and generate a cleanup you run and verify yourself. The loop has four moves:
How it works, step by step
- Inventory the mess in words: list every problem you can find with its count — formats, blanks, duplicates, outliers.
- Define the target: the exact column types, formats and conventions the cleaned sheet must have.
- Fix in small passes, one change type per pass, so every step can be checked in isolation.
- Never fill: a value that does not exist in the source becomes a visible marker, never a guess.
- Spot-check before analysis: recount rows, compare totals before and after, and sample five rows against the original.
That last move is the one people skip — and it is the one that catches the pass that quietly changed 200 values. Treat the cleanup itself like a dry run: show the list of intended changes before anything is applied permanently.
The never-fill rule
A missing region, an empty price, a blank date — none of these have a value hiding in them. Producing one anyway is imputation, and silent imputation is how a clean-looking dataset starts lying. If a gap must be filled for the analysis, that is an explicit decision: keep the raw column untouched, fill a separate one under a documented rule, and report how many values it added.
For AI-assisted work the rule becomes operational: gaps get a placeholder like [MISSING], and the cleanup report lists them. Asking for that marker is also the honest test — a model that would rather guess than mark is the wrong assistant for data work.
A bad example
Ask: “Here are my problems — dates, blanks, duplicates. Give me the formulas to fix everything and fill in the gaps so the summary is complete.”
“Fill in the gaps” authorises invention. You will get a complete-looking summary built partly on numbers that never existed, with no record of where they came from.
A better example
Ask: “Here are the problems with counts: mixed date formats in order_date (612 rows), blanks in region (214), suspected duplicates in order_id (38), mixed decimal separators in amount. Target: typed columns, ISO dates, nothing filled. Give me the fix steps in order, each with a verification check, and a list of the rows each step would change. Missing values become [MISSING].”
Every step is now a claim you can check against the data — and the [MISSING] marker makes the remaining uncertainty visible instead of disguised.
Practice
Assemble a data-cleanup prompt
Your orders export has the usual damage: order_date mixes two date formats, region has 214 blanks, order_id contains 38 suspected duplicates, and amount mixes dot and comma as decimal separators. You need a cleanup plan you can run and verify — not a magic clean file.
Fill the fields to assemble a cleanup request with the mess, the target state, the safety rules and the checks — then compare with a strong example.
A strong example (self-assessment — not machine-graded)
My orders export has these problems: order_date mixes two date formats across 612 rows; region has 214 blanks; order_id contains 38 suspected duplicates; amount mixes dot and comma as decimal separators. Target state: typed columns with ISO dates (YYYY-MM-DD), one decimal separator, duplicates listed in a separate sheet rather than deleted, and no filled values anywhere. Rules: never invent or impute a value — every missing value stays visible as [MISSING]; do not delete or merge duplicate rows, just report them with their row numbers; do not change anything I have not described. Give me the cleanup as ordered passes, one change type per pass (dates → decimal separators → duplicates → types). For each pass: the exact transformation or formula, the count of rows it would affect, and one spot-check I can run to confirm the pass did what it claims. Finish with a before/after comparison plan: which totals I should expect to stay identical.
Hint
Problems with counts turn vague cleaning into a testable plan — the counts are the specification.
The check field is where the cleanup stops being trust and becomes verification: counts, spot-checks, before/after totals.
Why this is the answer
The assembled prompt converts "clean this" into a verified process: the inventory with counts, a target state, a never-fill rule and a check per pass. Each pass is an isolated change with its own count — so when a total moves that should not have moved, the pass that caused it is findable in minutes instead of discovered in a meeting.
Transfer
- Before any analysis: write the problem inventory with counts — it doubles as your cleanup plan.
- One change type per pass: format fixes, then duplicates, then types — never everything at once.
- Compare totals before and after; a cleanup should not move the numbers, only the formats.
- If a gap must be filled, fill a copy under a stated rule and keep the raw column.
Key takeaways
- Most of analysis is cleaning — and cleaning is a workflow you verify, not a prompt you trust.
- The never-fill rule: missing values get a marker, never a guess.
- Every cleanup pass needs its own check; spot-checks catch the pass that quietly changed values.
Next
That completes the work track. Keep going on the practice floor, or check where you stand with the skills check. Terms: dry run, placeholder and rubric.