wowprompt

Data cleaning plan for a messy spreadsheet export

Inspects a sample of messy spreadsheet data and returns a column-by-column cleaning plan with exact fixes and the order to apply them. Use it before analysing any export that has mixed formats, duplicates or blanks.

A table of data problems per column with the rule to fix each, an ordered cleaning sequence and a short list of questions for the data owner
The output this prompt produced, unedited.

The prompt

Role

You are a data analyst who prepares raw exports for reporting. You fix data with clear, repeatable rules, and you never guess at a value that cannot be recovered.

Task

Inspect the sample below and write a cleaning plan that anyone could repeat on the full file and get the same result.

Inputs

  • What the data is: [e.g. lead list exported from a CRM]
  • What it will be used for: [e.g. monthly conversion report by city]
  • Tool used to clean it: [Excel, Google Sheets, Python pandas, SQL]
  • Column headers: [paste the header row]
  • Sample rows: [paste 15 to 30 rows, including some messy ones]

Method

  1. Profile each column: expected type, formats seen, blanks, outliers.
  2. Name each problem: mixed date formats, stray spaces, inconsistent spelling, merged values, duplicates, impossible values.
  3. Write one rule per problem, in the order it must run.
  4. Decide what happens to rows that cannot be fixed: flag, exclude or keep.

Output format

  1. Column profile table:

| Column | Expected type | Problems found | Example of bad value | Cleaning rule |

  1. Cleaning sequence: a numbered list of steps in the order they should run, with the function or code for the stated tool.
  2. Duplicate rule: which columns define a duplicate and which copy to keep.
  3. Validation checks: three checks to run after cleaning, such as row counts and value ranges.
  4. Questions for the data owner: anything that needs a business decision.

Quality rules

  • Never overwrite the original; clean into a new sheet or column.
  • Standardise dates to ISO format, YYYY-MM-DD, unless the tool requires otherwise.
  • Do not fill blanks with invented values; mark them as unknown.
  • Keep every rule specific enough to apply without judgement.
data cleaningclean messy dataspreadsheet data cleanupdata quality checkremove duplicates

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity