How to clean messy spreadsheet data with AI

Data cleaning is the perfect AI job: boring, rule-based and easy to check. The trick is to make the AI tell you what it changed, so you are never guessing.

Pick your route

• ChatGPT (file upload): best for one-off clean-ups. Upload a CSV or Excel file, describe the mess, and download a clean copy.
• Copilot in Excel: best if the file must stay in Excel and you have a Microsoft 365 plan that includes it.
• Gemini in Google Sheets: best if your data lives in Sheets already.

Step 1: Let the AI find the mess first

Don’t start by describing every problem. Ask the AI to audit the file.

I’ve uploaded a spreadsheet. Before changing anything, inspect it and list every data quality problem you find: duplicates, inconsistent spellings, mixed date formats, empty cells, numbers stored as text, extra spaces. For each problem, show three example rows and how many rows are affected.

You will often find problems you didn’t know you had.

Step 2: Clean with a change log

Fix the problems you listed, using these rules:

• Dates as YYYY-MM-DD.
• Company names in a single consistent spelling (show me the mapping you used).
• Remove exact duplicate rows; flag near-duplicates in a new column instead of deleting them.

Give me the cleaned file to download, plus a short change log: what you changed and how many rows each change affected.

The change log is your safety net. If “deleted 312 duplicates” sounds too high, ask to see them before you accept the file.

Step 3: Make it repeatable

If you get the same messy export every week, ask: “Write me an Excel formula (or a Google Sheets formula, or a short Python script) that applies these same fixes.” Next week, you won’t need the AI at all.

Bottom line

Audit first, clean with a change log, and keep near-duplicates flagged. That turns an hour of manual fixing into ten minutes of checking.