Clean and standardize spreadsheet data with a log of every change
Cleans pasted spreadsheet rows, fixes formats and inconsistencies, and gives you a change log plus steps to repeat it in Excel.
The prompt
Clean the spreadsheet data below and show me every change you made.
Data: [PASTE rows with headers - or upload the file. Mask names, emails, and IDs if sensitive]
What the data is used for: [e.g. "monthly sales report", "importing into our CRM"]
Rules I already know: [e.g. "dates must be DD/MM/YYYY", "region must be North/South/East/West", "amounts in USD"]
My tool: [Excel 365 / older Excel / Google Sheets]
Work through these checks:
1. Whitespace, non-printing characters, and inconsistent capitalization.
2. Dates and numbers stored as text, mixed date formats, currency symbols, and thousands separators.
3. Inconsistent spellings of the same value ("NY", "New York", "new york").
4. Blank cells: which are truly missing vs. should be zero or "N/A".
5. Values that break my rules or look impossible (negative quantities, future dates, totals that don't add up).
6. Exact duplicate rows.
Return:
1. Cleaned data as a table (or the first 50 rows if it is long).
2. Change log: Column | Issue found | Rows affected (count and examples) | Transformation applied | Why.
3. Left alone on purpose: anything ambiguous I need to decide, marked [CONFIRM], instead of guessing.
4. Repeatable steps for my tool: the formulas (TRIM, CLEAN, PROPER, DATEVALUE, VALUE, SUBSTITUTE) or Power Query steps so I can rerun the clean next time.
5. A row count before and after, so I can confirm nothing was lost.
Rules: never delete a row or fill a missing value without listing it in the log. Do not change numbers that look odd but might be real - flag them instead.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- e.g. "monthly sales report", "importing into our CRM"
- Excel 365 / older Excel / Google Sheets
- CONFIRM
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Explain a dataset or report in plain business languageExplains what a dataset or report contains, what each column or metric means, and what it shows, in words anyone can follow.
- Find what my dashboard is not telling meReviews a dashboard for blind spots, missing comparisons, vanity metrics, and misleading views, and suggests what to add.
- Flag impossible or suspicious values in my dataChecks your data against logic and business rules for impossible, placeholder, and implausible values, and gives reusable validation rules.
- Create a data-quality checklist for a recurring report or datasetA reusable pre-publish checklist with checks, formulas, pass thresholds, owners, and what to do when a check fails.