Clean and Standardize Messy Spreadsheet Data with Exact Formulas
Get a column-by-column cleaning plan with ready-to-fill formulas for spaces, case, dates and duplicates, plus a list of rows that need a manual check.
You getCleaning plan table, duplicate check and rows to review by hand
Prompt
You are a data analyst who cleans spreadsheets for reporting teams.
I will paste a sample of my messy spreadsheet data, with headers, right after this prompt. Before answering, ask me in one message:
1. Do I use Excel or Google Sheets?
2. About how many rows are in the full dataset?
3. What date format and text case do I want in the final data?
Wait for my answers, then build a cleaning plan.
Rules:
- Cover every column. Give an exact formula that works in my tool, written for row 2 so I can fill down. Write "no change needed" where that is true.
- Check for extra spaces, inconsistent case, mixed date and number formats, blanks and duplicates.
- Never guess a value. If a cell is ambiguous, such as 03/04/2024 or a phone number with missing digits, flag the row instead of fixing it.
- Define duplicates explicitly by naming the columns that must match.
- Leave the original data untouched. Put all formulas in new helper columns.
Output format:
1. Cleaning plan table: Column | Problem found | Formula or step | Before -> After example
2. Duplicate check: rule and formula
3. Rows to review by hand: Row | Value | Why it cannot be fixed safely
4. Final steps, in order
End with one line on the assumptions you made and what the sample might not show.Example output
Tips and common edits
Why this works
- Asking for Excel vs Google Sheets first means formulas actually run in your tool instead of failing on syntax differences.
- The never-guess rule turns ambiguous dates and broken phone numbers into a review list instead of silent errors.
- Helper columns and a paste-as-values final step keep your original data safe until you have checked the results.
Use this when you export or paste a messy list into Excel or Google Sheets. Have 10-30 representative rows with headers ready to paste into ChatGPT, Claude or Gemini.
Common edits
- Add your own duplicate rule, such as matching on name plus phone instead of email.
- Ask for a Power Query or Apps Script version if you clean the same export every week.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Build an Invoice Template in Excel or Google Sheets with Automatic Totals
You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.
Build Excel and Google Sheets Formulas for Workdays, Ages and Deadlines
You get working date formulas for your exact layout, such as workdays, deadlines, ages and month offsets, with a holiday table, locale pitfalls and a test row.
Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel
You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.
Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts
You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.
Build a Power Query Workflow That Combines and Cleans Monthly Excel Files
You get numbered Power Query steps with paste-ready M code, a full query, and a flag on every step that breaks when column names change.
Design a One-Page Manager Dashboard in Excel or Google Sheets
Get a buildable one-page dashboard plan with a layout grid, a spec for each tile (metric, chart, filter, source range, formula) and numbered build steps.