Data cleanup plan prompt: free ChatGPT template
Updated · By Robert Breen
Use this when a sheet has grown by hand or from a form and the same thing is written five ways: "French press", "french press", "FP" and a blank. You can't filter, count or send personal emails from data like that. Instead of asking the AI to rewrite thousands of rows, which is where values get changed by accident, this prompt asks for a plan: the standard value for each column, a mapping from the messy versions, a rule for anything that doesn't fit and the steps to apply it with your spreadsheet's own tools.
Work on a copy and keep the original columns until you have checked the result.
The prompt
Copy it into ChatGPT (or Claude, Gemini or Copilot) and replace every [BLANK] with your own details. It uses the four parts from Prompt Writing 101: Role, Context, Task and Format.
Role: You are a data operations specialist who cleans small business spreadsheets without losing or inventing information. Context: I use [APP]. The sheet is [SHEET]. The messy columns, with a sample of the different values that appear in each: [MESSY COLUMNS]. What the data is used for: [USE]. Values that must never be changed: [PROTECTED]. Task: For each messy column, propose a few standard values and map every messy value I gave you to one. Anything you can't map with confidence goes to "Needs review", never a guess. Suggest a rule for blanks. Then write steps to apply it in my app with a new helper column, find and replace or a lookup formula, leaving the original column and all protected columns untouched. Format: One section per column: Standard values, Mapping table (messy value, standard value), Blanks rule. Then a numbered list "Apply it", and a short checklist "Before you delete the old column".
Fill in the blanks
[APP]- Google Sheets or Excel.
[SHEET]- The sheet name and roughly how many rows it has.
[MESSY COLUMNS]- Each messy column with the different spellings and formats you see. Sorting the column A to Z makes them easy to spot.
[USE]- What the clean data feeds, such as email segments, a report or an import into another system.
[PROTECTED]- Columns that must stay as they are, like email addresses, amounts or IDs.
Example, filled in
A made-up example from the free lesson Build a Lead Magnet Signup Form with an AI Welcome Email. Harbor & Pine Coffee Roasters is the made-up coffee brand from the lead magnet lesson, where a "Get the free guide" form saves signups to a Leads sheet with Name, Email, Interest and Brew Method columns. Older signups were typed in by hand, and the values below are made up.
Role: You are a data operations specialist who cleans small business spreadsheets without losing or inventing information. Context: I use Google Sheets. The sheet is Leads, about 900 rows. The messy columns, with a sample of the different values that appear in each: Brew Method has "French press", "french press", "FP", "pour over", "Pour-over", "V60", "drip machine", "Mr Coffee", "espresso", "idk", and blanks. Interest has "less bitter", "less bitter cup", "stronger coffee", "gifts", "gift ideas", "Christmas gift", "learn to brew", "everything". What the data is used for: splitting leads into email segments for the welcome series. Values that must never be changed: Name and Email. Task: For each messy column, propose a few standard values and map every messy value I gave you to one. Anything you can't map with confidence goes to "Needs review", never a guess. Suggest a rule for blanks. Then write steps to apply it in my app with a new helper column, find and replace or a lookup formula, leaving the original column and all protected columns untouched. Format: One section per column: Standard values, Mapping table (messy value, standard value), Blanks rule. Then a numbered list "Apply it", and a short checklist "Before you delete the old column".
What a good answer looks like
- Brew Method narrows to a few values such as French press, Pour-over, Drip, Espresso and Not sure.
- "FP" and "V60" are either mapped with a stated reason or sent to Needs review, not silently guessed.
- "everything" in Interest goes to Needs review or a stated catch-all, since it can't be split.
- The steps use a new column with a lookup against a small mapping tab, leaving Brew Method itself untouched.
How to check it before you use it
An AI draft can sound right and still say something you never told it. Use the habit from the Reply Faster lesson: check every promise, name, date and number against what you gave it before anything goes out.
- Make a copy of the sheet before starting, and check the row count is the same after the cleanup.
- Sort the new column and scan the unique values. Anything outside your standard list means a mapping was missed.
- Compare Name and Email before and after on a handful of rows to confirm nothing protected changed.
- Fix the source too: change the form to a dropdown so new signups arrive clean.
Practice it in the free lesson
Build a Lead Magnet Signup Form with an AI Welcome Email: Build a "Get the free guide" signup form in Lovable for Harbor & Pine Coffee Roasters and connect it to n8n — the AI agent tags each lead by interest, saves it to Google Sheets and emails a personal welcome with the… You do every step yourself in a practice copy of Lovable and n8n, and nothing touches your real accounts.
Also useful: Build an n8n AI Agent That Plans Your Content Calendar · AI Receipt Extractor: Receipts to Google Sheets with n8n
Terms used here
Related prompts
- Spreadsheet formula helper prompt: Get a Google Sheets or Excel formula for the job you describe, explained piece by piece, with a quick way to test it on a copy…
- Google Sheets columns plan prompt: Plan the Google Sheets columns for an n8n automation: header names, what goes in each, who fills it, and which must match your…
- Duplicate invoice check prompt: Scan the bills in a payment run for likely duplicates: same vendor and amount, near-matching invoice numbers and bills already…
More sops and operations prompts
- Emergency service request triage prompt: Sort a batch of plumbing service requests by your own priority rules, flag unclear ones to call, and draft first replies using…
- Expense policy question reply prompt: Answer an employee's expense policy question before they spend: quote the rule that applies, say what to do, and flag what the…
- Incident report prompt: Draft an internal incident or near-miss report from your notes and witness statements: facts in order, who said what, gaps…