# 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.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Advanced
- Tags: Data & Analytics, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/clean-and-standardize-spreadsheet-data-with-a-log-of-every-change

## Prompt

```text
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.
```

## What to fill in

- e.g. "monthly sales report", "importing into our CRM"
- Excel 365 / older Excel / Google Sheets
- CONFIRM

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.