Lesson 5 · 11 min

Clean up a messy spreadsheet

By the end

A clean copy of a real spreadsheet, a list of every change, and the original untouched.

1Learn
2Try it
3Human check
4Quick check

Learn · 3 min

Ask for the steps, not the cleaned data

The tempting move is to paste the whole sheet into a chat and ask for it back, cleaned. Don’t. When an AI retypes your data, it can quietly drop a row, round a number or swap day and month, and a tidy-looking table hides it.

Ask instead for step-by-step instructions and formulas, and run them yourself in a copy. The spreadsheet makes every change, so each one sits in a cell you can see, check and undo. You also learn the fix, which helps when next month’s export arrives with the same mess.

If your spreadsheet has a built-in assistant, the same rule applies: point it at a copy and ask it to explain each change.

Describe the mess precisely

The AI only sees what you paste. Made-up names are fine: it needs the shape of the mess, not your customers. Give it:

The column headers, exactly as they are
About 10 sample rows that show the problems, with real names and contact details swapped for made-up ones
The problems you can see: mixed date formats, duplicates, stray spaces, numbers stored as text
What “clean” means for this job, like one row per customer, ready to import into your customer database

Keep the original, and prove nothing was lost

Duplicate the sheet before you start, and never edit the original. In the copy, put fixes in new helper columns rather than overwriting values, and flag duplicates in a column before you delete any.

Count the rows and total one key number column in both versions. Every difference should match a step you chose, and you should be able to point to the rows.

Give dates a second look. 03/04 can mean March 4 or April 3, depending on the format. Check a date after the 12th of a month: if it shows up as text or an error, the sheet is reading day and month in the wrong order, and any date with a day of 12 or less may have been swapped silently.

In one sentence

The AI writes the steps, the spreadsheet makes the changes, and the original stays untouched so you can check every one.

Try it on your business

8 min

Use a real sheet that slows you down: a customer list, a price list, or an export from your booking or sales system. If the mess is big, fix one problem today.

1
Duplicate the tab, call the copy “Clean” and leave the original exactly as it is.
2
Add a tab called “Checks” that counts the rows and totals your main number column in both tabs, like =COUNTA(Sheet1!A:A) and =SUM(Sheet1!D:D), using your own tab and column names.
3
Fill in the prompt with your headers, 10 sample rows and the problems you see, then run it.
4
Follow the steps one at a time in the Clean tab, checking a few rows after each.
5
Compare the counts and totals. Where one changed, find the rows and confirm each change was one you chose.

I’m cleaning up a spreadsheet in [Google Sheets or Excel]. It’s used for [what the sheet is for].
Here are the column headers and about 10 sample rows, with names and contact details made up: [paste]
The problems I can see: [e.g. mixed date formats, duplicate customers, extra spaces].
Clean means: [e.g. one row per customer, dates in one format, ready to import].
Don’t send back cleaned data. Give me numbered steps I can follow myself in a copy of the sheet:
1. For each step, say which column it changes, the exact formula or menu action, and why.
2. Put results in new helper columns instead of overwriting the original values.
3. Flag duplicates in a new column instead of deleting them.
4. End with how to check that no rows or amounts were lost.
Mark anything you’re unsure about with [CHECK].

Open ChatGPT Open Claude Open Gemini

Human check: before you use it

Check the original tab is untouched, and that the Clean tab’s row count and totals match it or every difference traces to rows you can point to.
Spot-check five rows against the original: the first, the last and three that the steps changed.
Check a date after the 12th of a month, so you know day and month weren’t swapped.
Delete nothing until a person has looked at the flagged rows.
Resolve every [CHECK] before the clean copy goes to anyone else or into another system.

Quick check · question 1 of 2No grades

Why ask for steps and formulas instead of a cleaned-up table?

Your notes · private, saved on this device

Ask the Distillery

Answers come from The Distillery’s own pages, with sources.

If the Distillery doesn’t know, it says so and points you to a person.