Data Cleaner: three moves that fix 90% of messy spreadsheets
Deduplicate, standardize, validate. Copy the formulas into a helper column and drag — no macros, no paid tools.
Every messy spreadsheet has the same three diseases: duplicates that double-count your totals, inconsistent text (trailing spaces, mixed casing, typos in company names), and values that are just wrong. Fix them in this order — never skip step 1.
Move 1 — Deduplicate
- Find duplicates first: add a helper column and use
— every row showing 2+ is a repeat. Delete extras by hand so you control which copy survives.=COUNTIF(A:A, A2) - Or extract unique values: in a blank column,
returns the clean distinct list.=UNIQUE(A2:A500)
Move 2 — Standardize text
— removes extra and trailing spaces (the invisible bug behind most failed lookups).=TRIM(A2)
— fixes all-caps or all-lowercase names to Title Case. Use =UPPER() for codes.=PROPER(A2)- Then copy the cleaned column, right-click and Paste Special as Values — otherwise it lives only as formulas.
Move 3 — Validate
— email addresses should be the same shape; a row of length 3 vs 25 stands out immediately.=LEN(A2)
— check each email appears exactly once.=COUNTIF(EmailCol, A2)
AI prompt for the weird jobs
I have a spreadsheet with columns [A, B, C]. Column A has inconsistent company names (mixed spacing, typos, legal suffixes). Write the exact Excel formula to clean it, then list the 5 rows that still look wrong. Sample data: [paste up to 50 rows].Order matters: deduplicate first, standardize second, validate last. If you standardize before dedup, two copies of the same row stay two copies; if you validate before standardize, half your flags turn out to be trailing spaces. Run the three moves in this order and the spreadsheet itself tells you where the mess is.
For recurring mess (a weekly export, a CRM dump), do not re-clean by hand each time. Write the five helper columns once, and every new paste flows through them. Spend two hours building the pipeline once, instead of forty minutes every Monday forever.
And finally: after cleaning, glance at one row end to end. A cleaned spreadsheet that still wrongs one record is worth less than the messy one you understood. Trust, then verify.
How it works
- Copy the sheet, then find duplicates with COUNTIF — never delete before you have a backup.
- Run TRIM and PROPER in helper columns, then paste the results back as plain values.
- Validate with LEN and COUNTIF, and use the AI prompt for anything formula-based you cannot solve.
No. The three moves here use five simple formulas: TRIM, PROPER, UNIQUE, COUNTIF and LEN. Paste them into a helper column and drag down — that is the whole skill set.
Paste up to a few hundred rows for real cleanup. For thousands of rows, use formulas in the spreadsheet itself; AI is best for spotting patterns and writing the formula, not for processing the whole file.
Merge first into one sheet, then clean. It is easier to find duplicates across two files after they live in one place, and you only run the formulas once instead of twice.
Add a helper column and convert: =DATEVALUE(SUBSTITUTE(A2,".","-")) for dot dates, then format the column as a date. If dates are inconsistent across rows, paste the sample column to AI and ask for one unified rule.
Yes — which is why you copy the sheet first (right-click tab, Move or Copy, tick Create a copy). Clean the copy, keep the original untouched. Deleting a duplicate should never mean losing the only record.
Who this tool is for
This is for the person who receives a spreadsheet rather than builds it: the HR coordinator handed a training attendance export, the finance assistant given supplier lists from three systems, the admin assembling a mailing list out of event sign-ups. You own the sheet for an afternoon, you have to make it usable, and nobody is going to rebuild the source system on your behalf.
Be clear about what it is not. Three formulas in a helper column are a spot fix for one file, not a data governance programme. If the same errors arrive every week from the same source, the durable fix belongs upstream — a validation rule, a required field, a corrected export template — and that conversation needs the system owner. Nor is this a substitute for a privacy or records review: pasting staff or customer data into a chat tool is a policy question, not a cleaning question. And if you are matching people or companies across two systems by fuzzy rules, you need a proper matching tool and a named owner for the results; a formula column will happily produce confident wrong matches.
How to read your result
Judge a cleaned sheet by explained flags, not by a tidy appearance. Record four numbers before and after — total rows, distinct values, rows changed by the text formulas, rows still failing validation — and expect every remaining flag to have a reason you can say out loud. A blank validation column usually means the check was never run.
| Check | Healthy return | What a bad return means |
|---|---|---|
=COUNTIF(A:A,A2) | Most rows show 1 | Any 2 or more is a duplicate you have not resolved |
=LEN(A2) | A narrow band of lengths per field | Lengths of three or four in an email column are truncated records |
=UNIQUE(range) | The count matches your expected list | A higher count than expected means case or spacing survived |
Then pick one row and read it end to end: name, email, department, date. If the row makes sense as a record of a person or an order, the sheet is probably sound. If it reads like fields that merely happen to sit next to each other, the cleaning removed syntax but not the structural problem.
A worked example
A training coordinator received a 412-row attendance export from a learning platform, with employer names typed by learners themselves. She copied the sheet first, then ran the three moves in order.
| Step | What she ran | Before | After |
|---|---|---|---|
| Find duplicates | =COUNTIF(B:B,B2) on the email column | 412 rows | 18 rows flagged, 394 distinct |
| Standardise text | =TRIM(C2) then =PROPER(...) | 57 rows with stray spaces, 6 spellings of one employer | One spelling, spaces gone |
| Validate | =LEN(B2) | 3 rows under six characters | 3 rows quarantined for follow-up |
She pasted the helper columns back as values, kept the 412-row original on a hidden tab, and sent the 391 usable records with a note listing the three quarantined rows and the eighteen duplicates she had removed. That note is what made the file trustworthy: the recipient could see which decisions had been made and disagree with any of them.
Common mistakes
- Deleting before copying the sheet. One wrong sort or an over-eager delete and the only record is gone; the copy costs ten seconds.
- Standardising before deduplicating. Two rows that differ by a trailing space will not match, so your duplicate check silently passes them.
- Leaving the helper column as formulas. Sort, filter or send the file and the values recalculate or break; paste special as values.
- Running the checks on the raw column. A unique list treats "Acme Ltd" and "acme ltd " as two entries, which is technically true and practically useless.
- Cleaning each export separately, then merging. Duplicates across two files only become visible once both sit in one sheet.
- Asking an AI to clean thousands of rows. It returns text, not a modified file; use it to write the formula and to spot patterns, then run the formula yourself.