Pro Skill Packs logo PRO SKILL PACKS

Guides / Data and spreadsheets

How to clean a messy spreadsheet with an AI assistant

A safe routine for cleaning a messy CSV or sheet with an AI assistant: describe the mess, get a rule list, apply it to a copy, and check the totals before you trust it.

Duplicate customers, dates in four formats, "N/A" and blank and "-" all meaning the same thing. Cleaning that by hand takes an afternoon. An assistant can do it faster, but it can also change values quietly. The routine below keeps you in charge of what changes.

Before you start

Work on a copy. Keep the original untouched and name the new file with today's date. Remove anything private you do not need to share, such as emails or phone numbers, before pasting data into any online tool. If you cannot share the data, ask for the cleaning steps only and run them yourself.

Pasting a whole large sheet rarely works. Paste the header row and 20 to 40 rows that show the different kinds of mess.

Step 1: ask for a diagnosis, not a fix

Do not ask it to "clean this". Ask what is wrong first.

Below are the column headers and a sample of rows from a spreadsheet. Do not change any data yet.

1. For each column, say what you think it holds and list every format or spelling variation you can see (for example, dates written three ways, "UK", "U.K." and "United Kingdom").
2. List rows that look like duplicates, with the row numbers and why.
3. List values that look wrong or impossible (negative quantities, dates in the future, text in a number column).
4. List blanks and placeholder values such as "N/A", "-" or "null".
5. For each problem, propose ONE cleaning rule in plain words, and say if it is safe or needs a human decision (such as which of two duplicate rows to keep).

Do not guess what a value should have been. If it is ambiguous, say so.

DATA:
[paste headers and sample rows]

Step 2: decide the rules

Read the rule list and cross out any you disagree with. Decide the human calls yourself, such as whether "Jon Smith" and "John Smith" are the same person. Write down the final rules. This list is your record of what changed.

Step 3: apply the rules, and ask for a change log

Apply exactly these rules to the data below and no others:
[paste your final rules]

Return: (a) the cleaned data in the same column order, and (b) a change log with one line per changed cell: row number, column, old value, new value, rule used. Do not change anything not covered by a rule. If you are unsure about a cell, leave it as it was and add it to a list called "needs review".

DATA:
[paste]

For a big sheet, ask for a formula, a short script or a find-and-replace list instead of the data, and run it yourself on the copy.

Step 4: check it

  • Count the rows before and after. The difference should equal the duplicates you agreed to remove.
  • Compare totals for any number column, such as sum of revenue, before and after. Changes should be explainable from the log.
  • Sort each column and look at the first and last values.
  • Spot-check 10 rows from the change log against the original.
  • Open the "needs review" list and fix those by hand.

Limits

  • An assistant can miscount or drop rows in long pastes. That is why you check the counts and totals.
  • It does not know your business. It cannot tell which duplicate is the right one or whether a strange value is an error.
  • Dates like 03/04/2025 are ambiguous. Tell it whether you use day first or month first.
  • A sample may not show every kind of mess. Run the rules on the full sheet and look again.
  • For anything financial or legal, keep the original and the change log.

Browse all skills in the catalog