Cleaning messy registers
Listen to this lesson
Nigerian voice narration
Fictional training case. A Data Officer inherits a 300-row fictional sales register ('Lagos Depot Q1') full of mixed date formats (03/04 vs 4-Mar), 12 duplicate invoice rows, and 8 blank amount cells. Before building any report, these structural defects must be found and documented, not silently autocorrected. Work through the example, identify the evidence needed and practise the method with cleared or synthetic inputs. Any additional figures in the explanation are separate practice examples, not claimed rows in a downloadable file.
Worked example
In this fictional scenario, Officer Bello pastes a 20-row sample of the Lagos Depot register into an AI chat and asks it to list every data-quality problem visible: inconsistent date formats, duplicate invoice numbers, blank amount fields, and text ('N/A') sitting in a numeric column. The AI returns a structured list with row numbers. Bello does not apply fixes yet — she copies the register to a new tab labelled 'working copy', keeping the original untouched. Only then does she ask AI for step-by-step Excel formulas (TEXT, IF, COUNTIF) to standardise dates, flag duplicates, and highlight blanks, testing each formula on five rows before applying it to all 300.
The method
Separate diagnosis from repair: first prompt asks only 'list problems,' a second prompt asks 'fix them.' This two-step approach stops the AI from quietly patching errors (e.g., guessing a blank amount) before you've decided whether that row should be investigated, corrected, or excluded entirely.
Worked practice and common mistakes
An ambiguous date such as 03/04 needs its intended day and month confirmed before standardisation. Display formatting cannot decide that meaning. Two rows with the same invoice but different amounts may be a correction rather than a duplicate. Keep a change log with old value, new value, source and reason. A blank amount is unknown unless evidence establishes zero.
Review before using the result
Diagnose-then-fix prevents silent data loss. Always clean on a duplicate sheet. Flag impossible or missing values for human investigation instead of letting AI invent plausible-looking replacements for them.
Your workplace practice
Use the recommended free assistant with this lesson's fictional example. Here is a sample of my messy register. List all data quality problems you can see, then give step-by-step Excel/Google Sheets instructions to fix each: dates, duplicates, blanks, text in number columns. Keep a copy of the input and draft. Compare the result with the verification steps above, record any correction, and retain placeholders where evidence is missing. The expected result is a checked draft for human review, not an automatically approved decision. Course examples and practice responses are fictional; amounts, dates, rules and thresholds are not official guidance or supplier quotations.
Open Google Gemini (free tier) and paste this prompt there. Suggests cleaning steps and formulas in free chat; apply and verify them manually in your spreadsheet.
Here is a sample of my messy register. List all data quality problems you can see, then give step-by-step Excel/Google Sheets instructions to fix each: dates, duplicates, blanks, text in number columns.
Why this tool: Suggests cleaning steps and formulas in free chat; apply and verify them manually in your spreadsheet.
Hit a free limit? Switch to ChatGPT or Gemini and continue — the prompt works in any assistant.