Messy data can ruin good analysis. Dates are written five different ways, names have extra spaces, and the same customer appears twice with slightly different spelling.
This guide shows how to clean spreadsheet data with AI as a planning and explaining partner. You will get prompts to spot problems, fix and standardize formats, automate and check your work, and avoid mistakes like cleaning the original file. The AI suggests steps, and you apply and verify them on a copy.
Use the cheat sheet below as a quick reference. Then read on to see when to use each prompt and how it looks filled in.
How to Use These Prompts
Words in [brackets] are blanks. Replace each one with your own details before you send the prompt.
Before: Check [column] for mixed formats
After: Check the Date column for mixed formats: Jan 3, 1/3/2025, 3 January, 2025-01-03.
Paste a small sample of made-up or anonymized rows, not your whole private dataset. These prompts work the same way in ChatGPT, Claude, and Gemini. Always work on a copy of your file, and check every change before you rely on it.
Spot the Problems
Find problems in this data
Start by asking what looks wrong. Paste a small sample and ask the AI to flag blanks, duplicates, and odd entries. Review each flag, because the AI only sees what you paste.
Example: Find problems in this data: Jan 3, Mug, 12, 8.50. Jan 3, Mug, 12, 8.50. Jan 5, Tote, , 15.00. Jan 6, Mug, -4, 8.50.
Check [column] for mixed formats
Mixed formats cause errors in sorting and formulas. Paste a few values from one column and ask the AI to find inconsistencies. Then decide on one format to use.
Example: Check the Date column for mixed formats: Jan 3, 1/3/2025, 3 January, 2025-01-03.
List duplicates in [column]
Duplicates can inflate counts and totals. Ask the AI to point out repeated values in your sample. Review them by hand, since two similar rows may be different records.
Example: List duplicates in this column of customer names: Sam Lee, Sam Lee, Samuel Lee, Dana Cruz, dana cruz.
Describe each column: [sample]
Before you clean anything, check that the AI understands your data. Paste a few rows and ask for a description of each column. If it is wrong, add notes about what each column means.
Example: Describe each column from this sample: Date, Product, Units, Price. Jan 3, Mug, 12, 8.50. Jan 4, Tote, 5, 15.00.
Which values look wrong?
Some mistakes are not duplicates or blanks. They are values that do not make sense, like a negative quantity. Ask the AI to suggest values to verify, then check the source.
Example: Which values look wrong in this list of units sold: 12, 14, 11, -4, 13, 900, 12?
Fix and Standardize
Make dates consistent: [sample]
Pick one clear date format and apply it everywhere. Paste a sample and ask for a plan to convert all dates to the same style. Test the steps on a copy of your data first.
Example: Make dates consistent: Jan 3, 1/3/2025, 3 January, 2025-01-03. Suggest one format and explain how to apply it in a spreadsheet.
Split names into 2 columns
Names stored in one cell are hard to sort. This prompt explains how to separate first and last names. Test the method on tricky names, such as ones with middle names or suffixes.
Example: Split names into 2 columns for this list: Sam Lee, Dana Cruz, Mary Ann Parker, Jordan Smith Jr.
Fix capitalization in [column]
Mixed capitalization can stop matching and make reports look messy. Ask how to standardize text, such as using title case. Check for names that should keep unusual capitals.
Example: Fix capitalization in a column of product names such as mug, TOTE BAG, and Water Bottle.
Remove extra spaces in [column]
Stray spaces are invisible but can break matching. This prompt explains how to trim them. Test it on a few cells to make sure it works as expected.
Example: Remove extra spaces in a column where some entries have spaces before or after the text, such as " Mug " and "Tote ".
Merge duplicate rows safely
Merging duplicates can lose data if you are not careful. Ask for steps that keep the information you need. Make a backup copy before you delete anything.
Example: Merge duplicate rows safely when two rows have the same customer name but different phone numbers. Suggest steps that avoid losing data.
Automate and Check
Formula to clean [column]
A formula can fix many rows at once. Describe the problem and your spreadsheet app, and ask for a suggestion. Test it on a copy and compare the results with the original.
Example: Formula to clean the Name column in Google Sheets by trimming extra spaces and fixing capitalization.
Steps to clean this sheet
The order of cleaning steps matters. Describe your sheet and ask for a safe sequence, such as backup, remove blanks, fix formats, then check duplicates. Follow the steps one at a time.
Example: Steps to clean this sheet of 500 customer orders with columns for date, name, product, units, and price.
Add a check column for [rule]
A check column marks rows that break a rule you set. This helps you review problems quickly. Ask the AI how to set it up in your spreadsheet.
Example: Add a check column for units: flag any row where units are less than 1 or more than 100.
Compare before and after
After cleaning, compare your data to the original. Ask how to check row counts, totals, and key values. This helps you confirm that nothing important changed.
Example: Compare before and after: what should I check to confirm my cleaned sheet still has the same orders and total units?
Write cleaning notes
A short record of what you changed helps you explain the data later. Give the AI a list of steps and ask for a clear summary. Keep it with your file.
Example: Write cleaning notes from these steps: removed 12 blank rows, standardized dates, trimmed spaces, merged 4 duplicate customers.
Common Mistakes
Cleaning the original
If you edit the only copy, you cannot undo a mistake. Work on a copy so you can compare and recover. Quick fix: save a copy with “original” in the name before you start.
Pasting real private data
Do not paste real names, IDs, or contact details into an AI tool. Use a small sample with made-up or anonymized values. The method will work the same way on your real file.
Trusting one pass
A single round of cleaning rarely catches everything. Re-check results after each change. New problems can appear after a fix.
Deleting without a look
Do not remove flagged rows without reviewing them. A row that looks wrong may be correct. Review each flag before you delete.
Skipping a log
Without notes, you will not remember what you changed. Write down each fix as you go. A log makes your work easier to explain and repeat.
Pro Tip
Test every cleaning step on a small copy first. Check a few rows by hand before you run it on everything, so a mistake does not spread across your whole sheet.
Example: Here are five sample rows and the cleaned versions I expect. Tell me whether this method would produce the same results.
FAQ
Can ChatGPT clean my spreadsheet?
It can help you plan the steps, suggest formulas, and explain what to check. It cannot see or change your file directly. You apply the steps yourself and verify the results.
Is it safe to paste my data into AI?
Be careful. Use a small sample with made-up or anonymized values, and avoid names, IDs, and contact details. Check the privacy settings of the tool you use.
What should I do before cleaning data?
Make a backup copy and decide what a clean version should look like. Note the problems you see, such as mixed dates or duplicates. Then clean one issue at a time.
Final Thoughts
Clean data takes patience, but a clear plan makes it manageable. Use these prompts to spot problems, apply fixes safely, and document your work. Work on a copy, check every change, and keep private information out of the chat.
If this guide helped, save the cheat sheet on Pinterest and bookmark this page for your next messy spreadsheet.