Specify a spreadsheet clean-up before you do it
Describe a messy spreadsheet and get a written clean-up plan: issues found, fixes proposed, and rules you can apply consistently.
Yours to copy, change, and make your own.
Replace every [BRACKETED PLACEHOLDER] with your own material before you send it.
I have a messy spreadsheet I need to clean before using it. Below is a sample of rows (with any sensitive values replaced) and a description of what the data should look like. What the data is: [WHAT THE DATA IS] What clean should look like: [TARGET STATE] Sample rows: [PASTE 10-20 SAMPLE ROWS] Do the following: 1. List every data-quality problem you can see in the sample (formats, duplicates, inconsistent categories, impossible values, missing data). 2. For each problem, propose a fix as a written rule, for example "dates: convert all to DD/MM/YYYY; treat 01/02 ambiguity as UK format". 3. Flag any fix that involves a judgement call a human should make, and say what the options are. 4. End with a checklist I can follow, in order, including a final validation step. Do not invent columns or values that are not in the sample.
- [WHAT THE DATA IS]
- One or two sentences on where the data came from and what it represents.
- [TARGET STATE]
- What you need the clean version for, and any format rules the destination system imposes.
- [PASTE 10-20 SAMPLE ROWS]
- A representative sample including messy rows, with sensitive values swapped for fakes.
Where it shines, and where it falls over.
- Contact lists and CRM exports merged from several sources
- Survey exports with free-text and inconsistent categories
- Preparing data for a mail merge, import, or dashboard
- Replace names and emails with fakes before pasting; the structure is what matters, not the values.
- Include the worst rows you can find in the sample, not the tidiest ones.
The plan is only as good as the sample. Problems that do not appear in your 10 to 20 rows will not appear in the plan, and real spreadsheets keep their worst surprises in row 4,000. Treat the checklist as a first pass and spot-check the full file after cleaning.
The ambiguity warnings matter more than the fixes. If the model says a date column could be UK or US format, that is a decision only someone who knows the data source can make. Guessing wrong corrupts every row silently, which is far worse than an obviously broken column.
AI output is a first draft, not a finished product. You are responsible for whatever you send, publish, or decide with it.
The Institute of AI sets the standard of AI practice, works to that standard itself, and puts it within reach of everyone else. The AI for All Prompt Library is one of the ways it does that.
More prompts worth exploring.
Theme free-text survey responses
Turn hundreds of open-ended survey comments into a small set of evidenced themes, with counts and representative quotes.
Write an impact report
Turn activity data and beneficiary feedback into a report that separates outputs from outcomes and is honest about the gaps.
Write a grant application
Turn your project notes into a funder-ready answer: need, response, outcomes, and evidence, inside the word limit.