Clean a messy spreadsheet in 10 minutes with AI

Every small business has at least one spreadsheet that’s been slowly going feral — customer list, inventory, vendor contacts. Names half-capitalized, phone numbers in three formats, a date column where some are “3/4/24” and others are “March 4 2024”. You’ve been meaning to clean it for a year. AI does this in 10 minutes if you ask correctly.

When AI is the right tool here

This works for spreadsheets up to roughly 1,000 rows. Beyond that, you should use AI to write the formulas instead of doing the cleanup directly. For under 1,000 rows, paste-and-clean is faster than learning Excel functions.

The prep step (60 seconds)

Open your spreadsheet. Decide what “clean” means for THIS file:

What columns should exist (and in what order)?
What format does each column need?
- Names: Title Case
- Emails: lowercase, trimmed
- Phone: (XXX) XXX-XXXX or +country format
- Dates: YYYY-MM-DD
What should happen to genuinely missing data? (leave blank vs. fill with ”—”)
What should happen to obvious duplicates?

Write this down in 5 lines. AI needs a target to aim at.

The cleanup prompt

📋 Prompt — copy & paste v1

Clean the spreadsheet data below according to these rules. Output as
a markdown table I can paste back into my spreadsheet.

DESIRED COLUMN ORDER:
[column1, column2, column3, ...]

FORMAT RULES PER COLUMN:
- [column1]: [format rule, e.g. "Title Case names, no extra spaces"]
- [column2]: [e.g. "lowercase emails, trimmed"]
- [column3]: [e.g. "(XXX) XXX-XXXX phone format; flag invalid as INVALID"]
- [column4]: [e.g. "YYYY-MM-DD dates; ambiguous date formats — flag as REVIEW"]

GENERAL RULES:
- Trim whitespace on all fields
- Keep blank cells blank, do NOT invent missing data
- For obvious duplicates (same email, exact match): keep the row with the most
filled fields, drop the others, and add a note at the end listing what was
removed.
- For uncertain cases, keep the row but flag the suspicious cell with
[[REVIEW: reason]]
- Do NOT modify any column not listed above
- Preserve the header row exactly as I provided

DATA:
[paste full spreadsheet — copy as plain values, not formulas]

OUTPUT:
1. The cleaned table in markdown
2. A "Cleanup notes" section listing: total rows in / out, # of [[REVIEW]] flags
 and which rows, # of duplicates removed and which

Works in: ChatGPT · Claude · Gemini · Claude handles larger pastes more reliably; GPT-4 if rows < 300

Why this prompt works

Three things make it actually safe to use:

It refuses to invent data. Most spreadsheet cleanup horror stories come from AI “helping” by filling in plausible-looking but wrong info. The do NOT invent missing data line prevents this.
It flags uncertain cells instead of silently fixing them. [[REVIEW]] markers let you scan and verify in 30 seconds.
It preserves what you didn’t ask it to touch. The “do not modify columns not listed” rule keeps your formulas, comments, and structure intact.

After AI returns the table

Two checks before you paste back:

Row count match. If you sent 412 rows and got back 387, AI either deduped correctly (check the notes section) or truncated mid-output (re-run in smaller batches).
Spot-check 5 random rows against the original. Specifically check the [[REVIEW]] flags — those are where AI was honest about uncertainty.

When to break the data into chunks

If your spreadsheet is over ~300 rows, send it in chunks of 200–300:

Send rows 1–250 with the prompt
Then “continue with rows 251–500, same rules”
Concatenate manually

This is dramatically more reliable than one giant paste.

Tasks AI is good at on spreadsheets

Standardizing formats (the case above)
Categorizing free-text into predefined categories (e.g. tagging customer comments as “complaint / praise / question”)
Generating summary text from rows (e.g. “write a one-line summary of each customer based on these columns”)
Drafting bulk emails based on the data (“write a personalized email to each customer in the list, referencing their last order”)

Tasks AI is BAD at on spreadsheets

Math beyond simple arithmetic. Use formulas. AI will produce confidently wrong numbers.
Anything financial without a verification step. Bookkeeping, tax preparation, financial reporting — AI is a draft tool only, never an authoritative source.
Detecting fraud or anomalies in long transaction lists. It hallucinates patterns that aren’t there.

The headline: spreadsheets are about data integrity, and AI is great at formatting but not at numbers. Use it for the boring cleanup, not the math. You’ll save hours and keep your books accurate.