TL;DR
Paste a 10-20 row sample of the messy column, tell the AI exactly which transformations are allowed, and demand a diff log plus a review queue. Verify the sample, then scale. For one-off cleanup, ChatGPT’s file analysis (Plus, $20/mo as of June 2026) writes and runs pandas code on your .xlsx. For repeatable cleanup inside the file, Excel’s Clean Data (Microsoft 365 Copilot) fixes spacing, capitalization, and number-stored-as-text with one-click preview cards — but it will not remove duplicate rows or split columns. Never let AI silently change meaning; make it flag.
The task
You have an export — customer names, addresses, free-text tags, product titles — that arrived in spectacular condition: mixed case, leading whitespace, typos, and John Smith sitting next to john smith. You want a clean column to filter, join, or report on. Manual regex or Find & Replace is slow, and AI can do it in one prompt — but only if you sample first and verify before scaling.
When AI helps, and when it does not
AI is strong at fuzzy normalization, deduplication, and obvious-typo correction. It is weak and dangerous at changing meaning without flagging: it may “fix” Sun to Sunday, co to Colorado, or Apple to Apple Inc. — sometimes correctly, sometimes catastrophically. Under the hood, ChatGPT’s file analysis runs Python (pandas) in a sandbox, so it behaves like a junior data analyst: fast and literal, occasionally too confident. Always run on a sample, verify, and spell out which transformations are allowed.
Pick the right tool (June 2026)
| Tool | Best for | Limits / cost | Dedupe? | Split columns? |
|---|---|---|---|---|
| ChatGPT file analysis (Plus) | One-off cleanup of an exported file; full pandas flexibility | $20/mo; ~50 MB / file upload; runs sandboxed Python | Yes, exact + near | Yes |
| Excel Clean Data (M365 Copilot) | In-place fixes you trust without re-uploading | Needs M365 + Copilot license; best ≤ 50,000 rows / 100 columns; English works best | No (use Remove Duplicates / Power Query) | No (use Text to Columns) |
| Gemini in Google Sheets | Cleanup that stays in the sheet; multi-step edits | Spreadsheet upload needs Google AI Pro ($19.99/mo); ~50k rows comfortable | Yes, via prompt | Yes |
| Claude (Pro) | Iterative, careful cleanup with a clear audit trail | $20/mo; 30 MB / file; reliable diff logs | Yes | Yes |
Excel’s Clean Data lives on the Data tab: it scans a table and shows suggestion cards for spacing (Microsoft Excel → Microsoft Excel), capitalization (EXCEL → Excel), and numbers stored as text. It does not delete duplicate rows or split combined fields — for those, use Remove Duplicates, Text to Columns, or Power Query. For anything fuzzy (typos, “is J. Smith the same as John Smith?”), a chat model with file upload is more capable.
What to feed the AI
- A small sample (10-20 rows) of the messy column
- The desired output format (
lowercase,Title Case, exactFirstname Lastname) - What is not allowed to change (“do not expand abbreviations,” “do not infer country”)
- A small “good output” example so the AI knows what success looks like
- Any reference list to match against (a controlled vocabulary, a list of valid cities)
- Whether to flag uncertain rows or make a best guess
Copy-ready prompt
Clean this messy text column.
Desired output: [format and examples]
Allowed transformations: [normalize case / trim whitespace / fix obvious typos / dedupe]
Not allowed: [expand abbreviations / infer missing fields / merge names / change meaning]
Reference list (must match if applicable): [list]
Unsure rows: [flag with [REVIEW: reason] or best-guess]
Sample (10-20 rows):
"""
[paste]
"""
Good output example:
"""
[paste 3-5 cleaned rows]
"""
Return:
1. Cleaned sample, row-aligned with the input
2. A "diff log" — for every row you changed, the original, the new value, and the reason
3. A "review queue" — rows where you were unsure
4. Whether the same prompt is safe to apply to the full column
Do not change a value to fit a pattern unless you are confident. Flag instead.For 1000+ row jobs, add: “Once I confirm the sample, give me the exact transformation rules (a Power Query script, a pandas snippet, or formulas) so I can run them deterministically, not by re-prompting.”
Why the diff log matters
The single most valuable output is the diff log (original / new / reason). Without it, mistakes propagate invisibly — a column looks clean while three rows quietly changed meaning. The review queue is the companion safeguard: flagged rows are not failures, they are the careful path. An AI that returns zero unsure rows on real-world data is overconfident, not perfect.
How to check the output is usable
- Run the same prompt twice on the same sample. Outputs should match. Variation means the rules are underspecified.
- Spot-check 5 random rows in the diff log against the raw input.
- Confirm a review queue exists, and that flagged rows really are ambiguous.
- Reference-list matching is exact — no near-matches slipping through unflagged.
- No row’s meaning changed.
sun→Sunis just case;Sun→Sundayis a meaning change and should never happen silently.
Common mistakes
- Pasting the whole column without sampling. You discover errors only after they are everywhere.
- No “good output” example, so the AI guesses at your intent.
- Letting AI expand abbreviations.
USA→United States of Americabreaks downstream joins. - No diff log, so corrections happen invisibly.
- Treating the review queue as a defect instead of a feature.
- Deduping in Excel Clean Data and assuming it removed rows — it does not. Use Remove Duplicates or Power Query.
FAQ
- Should I use AI or formulas? Formulas (or Power Query) for deterministic rules; AI for fuzzy work. Use both: AI to find the patterns, then a generated formula or Power Query script to scale them deterministically.
- What’s the row limit? Excel’s Clean Data performs best within 50,000 rows and 100 columns. ChatGPT and Gemini both handle roughly 50,000-row files comfortably; for larger or iterative jobs, have the AI emit a script and run it locally.
- What about PII? Be cautious with names, emails, and IDs. Hash or pseudonymize sensitive fields before pasting, and prefer the in-file tools (Excel Clean Data, Gemini in Sheets) that keep data inside your account.
- Will AI dedupe based on similarity? Yes, but verify the similarity rule.
John SmithandJ. Smithmay or may not be the same person — make the AI flag near-matches rather than auto-merge them. - Why does my cleaned column still look off? Clean Data is tuned for English; mixed-language or diacritic-heavy columns may need a chat model or a Power Query step instead.
Related
- Table explanation — make a clean table digestible
- Chart takeaway — once cleaned, summarize the chart
- Spreadsheet error diagnosis prompts — when formulas misbehave
- Excel formula explanation prompts — understand existing formulas
- Excel analysis prompts — deeper analysis after cleanup
- ChatGPT spreadsheet help — tool walkthrough
- AI table explainer tutorial — full table workflow
External references: Microsoft — Clean up your data with Copilot in Excel and the pandas documentation (the library ChatGPT uses behind the scenes).