How to Clean Messy Spreadsheet Data With AI

The safest way to clean messy spreadsheet data with AI is to let the AI propose cleaning rules and write the formula or script, then apply them yourself and compare before and after. Do not paste a sheet in and ask for a clean one back. Models can quietly change values, drop rows, or "fix" things that were correct, and you will not see it.

This guide uses a made-up customer sheet with the problems nearly every real one has: mixed date formats, inconsistent capitalisation, phone numbers in five styles, and near-duplicate rows.

Step 1: Describe the mess, do not hand over the data

Paste 15 to 20 representative rows, including the ugliest ones, and strip anything personal first. Then ask:

Here is a sample of a customer spreadsheet (columns: name, email, phone, signup_date, country).
List every inconsistency you see, grouped by column.
For each, give one example and the rule you would use to fix it.
Do not output cleaned data yet.

You get a list of problems and candidate rules. Edit the rules. You know that "UK" and "United Kingdom" are the same here, and the model does not.

Step 2: Turn the rules into formulas or a script

Ask for something repeatable, not a one-off rewrite:

Write Google Sheets formulas (or an Excel equivalent) that implement these rules in new columns,
leaving the originals untouched. Name each new column clean_<original>.

Keeping the original columns means you can always see what changed. If you have thousands of rows, ask for a short Python script that reads a CSV and writes a new CSV, and read it before running it.

Step 3: Check the result with counts

This is the step people skip. Before you trust anything, compare:

• Row count before and after. They should match unless you chose to remove duplicates.

• Count of blanks per column before and after. New blanks mean a rule failed.

• Number of cells that changed per column. A column where 90 percent of cells changed deserves suspicion.

• A random sample of 20 changed rows, checked by eye against the originals.

If you have a filter or pivot table, use it: a pivot on the cleaned country column should show a short, sensible list.

Step 4: Handle duplicates by flagging, not deleting

Ask the AI for a formula that marks likely duplicates, for example the same lowercased email, or the same name plus phone. Review the flagged rows yourself, then delete. Near-duplicates are judgement calls. "Ann Lee" and "Anne Lee" at the same address may be one person or two.

Mistakes to avoid

• Pasting the entire sheet into a chat. It is slow, it may leak personal data, and long pastes are where models drop rows.

• Letting the AI guess formats. Dates like 03/04/2026 are ambiguous. Tell it which convention applies.

• Cleaning in place. Always work on a copy.

Once the data is clean, our guide to prompting AI to analyze a spreadsheet picks up where this one stops, and turning a spreadsheet into an app with AI covers the next step if the sheet has outgrown being a sheet. If you are new to writing prompts like these, start with the prompt engineering guide. Because models can state wrong things confidently, the checks in how to tell if an AI answer is hallucinated apply here too.

Messy files are a problem beyond spreadsheets too. Organizing a messy shared drive with AI uses a similar review-first approach, and choosing an AI model for document extraction helps when the data is trapped in PDFs.

FAQ

Can ChatGPT or Claude clean a spreadsheet for me?

They can help a lot when they write the formulas or script and you verify the output. Asking them to rewrite the whole sheet directly is where silent errors appear.

How do I fix inconsistent data in Excel with AI?

Describe the inconsistencies from a sample, agree on rules, and have the AI write formulas into new columns. Then check counts and a sample.

Is it safe to upload customer data to an AI tool?

Not by default. Remove names, emails and phone numbers from samples, and check your tool's data retention terms before uploading real records.

How many rows can AI clean at once?

Pasting is unreliable beyond a few dozen rows. A formula or script scales to any size, which is why this method uses them.