Spreadsheet Data Cleaning Plan

Describe a messy spreadsheet and get a step-by-step cleaning plan with the exact formulas, plus the checks that prove you did not lose rows.

Act as a data analyst. Build me a cleaning plan for a messy spreadsheet.

The data:
- Tool: [EXCEL / GOOGLE SHEETS]
- Rows and columns: [APPROX ROW COUNT] rows, columns are [LIST COLUMN NAMES]
- Sample of the raw data (paste 5-10 rows including the header):
"""
[PASTE ROWS]
"""
- Known problems: [e.g., dates in three formats, duplicate customers, trailing spaces, numbers stored as text, blank cells]
- What I need the clean data for: [THE ANALYSIS OR REPORT]

Deliver:
1. A numbered cleaning plan, one problem per step, in the order that avoids re-work
2. For each step: the exact formula or menu path for my tool, written so I can paste it, and what the result should look like
3. A "do not do this in place" warning for any step that destroys the original values, with the safe alternative
4. Three validation checks I can run after cleaning: row count reconciliation, a total that should match before and after, and a spot check for the trickiest column
5. The judgement calls I have to make myself (which duplicate wins, how to treat blanks) with the trade-off for each — do not decide these for me
6. What I should fix at the source so this cleaning is not needed next month

How to use this prompt

• Paste real rows, not a description of them — format problems are invisible in prose.
• Work on a copy of the sheet and keep the raw tab untouched, exactly as step 3 says.
• Run all three validation checks before you use the data. A clean-looking sheet that silently dropped 40 rows is worse than a messy one.

Why this prompt works

Cleaning advice is only useful if it is specific to your tool and your columns, which is why the prompt demands pasted rows and formulas rather than principles. Separating the mechanical steps from the judgement calls is the key move: it stops the model from quietly deciding which duplicate to keep, which is a decision only you can make.

Variations to try

• Ask for the same plan as a Python/pandas script if the file is too big for a spreadsheet.
• Add "the file arrives weekly in this exact format" to get a repeatable macro or Apps Script instead.
• Ask it to write the validation checks as formulas that go in a separate QA tab.

Common mistakes to avoid

• Pasting sensitive customer data into a public AI tool. Anonymise or use dummy rows that share the same shape.
• Accepting a de-duplication rule you did not choose, then reporting numbers you cannot defend.
• Cleaning in place and losing the ability to check your work against the original.

Works well with