Clean a messy table before you analyse it Prompts

Plans the clean-up of a messy table, makes duplicates and inconsistent values visible and does not silently drop uncertain rows.

Area: Office & organisation
Level: Beginner
Good for: Prepare an exported list so filters and totals work again
Make inconsistent date, name and currency formats visible
Get a change list you can review row by row before the analysis

How it works

Paste a slice of the table, or the column headers plus typical rows.
Say what you want to analyse and which columns are essential for that.
Do not merge uncertain duplicates or invent missing values; propose a change you can check.

To copy

Help me clean a messy table before I analyse it. Do not delete or merge anything that is uncertain, and do not invent missing values.
Table excerpt, or headers with typical rows:
[paste table]
Purpose of the analysis: [purpose]
Essential columns: [columns]
Known problems (optional):
[paste notes]

Check and plan:
1. Structure: which columns mean the same thing, which are empty, which mix two facts in one cell?
2. Formats: where are dates, numbers, currencies, yes/no values or names inconsistent? Describe the rule without quietly rewriting the whole table.
3. Duplicates: which rows might be the same record? Mark certain and uncertain cases separately. Uncertain rows stay.
4. Gaps: which required fields are missing for the analysis? List them instead of filling them in.
5. Change list: numbered, checkable steps (rename, split, standardise, flag). Every change must be described so it can be undone.
6. Checks: three controls I can use after the clean-up to see that totals or counts still match the source table.

Output: findings, change list, uncertain cases, checks.