Guides › Documents & Data
Clean up a messy spreadsheet in minutes
Get a step-by-step clean-up plan for messy data, from duplicates and mixed date formats to inconsistent names, with formulas you can paste in.
When to use it
Before you analyse or share a spreadsheet
Step by step
Make a backup copy
Duplicate the sheet tab or file before you change anything, so you can always go back.
Show the AI a sample
Copy the header row and about 20 typical rows (including the messy ones) and paste them with this prompt.
Prompt to copyHere is a sample of my spreadsheet in [Excel / Google Sheets]. It has about [number] rows. Find the data problems: duplicates, inconsistent spellings (e.g. 'Ltd' vs 'Limited'), mixed date formats, numbers stored as text, extra spaces and blank cells. For each problem, give me: what it is, which column, and the exact step or formula to fix it in [Excel / Google Sheets], explained for a beginner. Number the steps in the order I should do them. [paste sample]Fix one problem at a time
Follow the steps in order. Use built-in tools where it suggests them, such as removing duplicates and trimming spaces from the Data menu in Google Sheets or the Data tab in Excel.
Check the row count
Compare the number of rows before and after. If lots disappeared, check the duplicates really were duplicates.
What you get
1) Column B has extra spaces: in a new column use =TRIM(B2), fill down, then paste as values. 2) Dates in column D mix 03/04/26 and 2026-04-03: select D and set the format to Date... 3) 46 duplicate rows by email: remove duplicates on column C.
Tips
- Use made-up or trimmed sample rows if the real data includes customer details; the AI only needs to see the pattern.