An original resource by Waseet Mahyudin
What belongs in a data-cleaning handover?
Hand over usable records with field definitions, a change log and unresolved issues. Includes a reusable cleaning agreement and reconciliation checklist.
Free to reuse for your own work. No signup or file upload needed.
Who this helps
For a small team preparing a spreadsheet, contact list or research dataset for review, reporting or an agreed CRM import.
A cleaned file whose changes can be explained, whose unresolved values remain visible and whose structure matches its intended use.
1. Preserve the starting point
Keep the original file unchanged. Work from a copy and give each source row a stable reference. Without that reference, a cleaner-looking sheet can be difficult to audit or correct.
- Record the source file, worksheet, received date, version and row count. State whether headers and blank lines are included in the count.
- Confirm that the data is client-owned, public or authorized for this task; agree access and delivery channels.
- Assign source row IDs before sorting, filtering or merging. Keep them attached to the resulting records.
- Record the target use and tool: a review spreadsheet is different from a CRM import with fixed field requirements.
2. Agree what “clean” means
Define changes before applying them. Formatting can be standardized; an unknown value cannot become known just because a column is required.
- Create a field dictionary with type, allowed values, whether blank is permitted and the source of any transformation.
- Store identifiers, postal codes and phone numbers as text where leading zeros or symbols matter.
- Agree date formats and locale. Flag an ambiguous value such as 03/04/2026 instead of silently choosing a month and day.
- Define a duplicate key and conflict policy. Similar company names alone do not prove two contacts are the same person.
- Keep missing values blank or use an agreed explicit marker. Do not infer an email address or add invented contact details.
3. Inspect transformations and exceptions
A repeatable rule needs an explanation and a way to review exceptions. Keep exact duplicate handling separate from possible matches that need a person’s decision.
- Log the field, original value, resulting value, rule and source row IDs for each changed record or clearly defined batch.
- For merged records, retain every source row reference and explain which value won when fields differed.
- Keep unresolved conflicts in an issue register with severity, owner and the next action.
- Compare input, retained output, merged duplicate rows and exclusions. Use separate counts for unresolved rows that remain in the output.
- Review representative records: unchanged, formatted, merged, missing and ambiguous. Expand review when an error suggests a wider rule problem.
4. Deliver a package someone else can use
A final spreadsheet is one part of the handover. The accompanying notes explain what is ready, what still needs a decision and how to avoid losing information on import.
- Deliver the cleaned file, field dictionary, change log, issue register and a short readme using agreed names and version numbers.
- Check the exported file by reopening it in the intended tool; inspect encoding, delimiters, line breaks, leading zeros and dates.
- If CSV is requested, explain that it does not retain workbook formatting or multiple sheets. Deliver separate files where the agreed package needs them.
- Treat strings beginning with spreadsheet formula characters as text when appropriate, and document the agreed export handling.
- Run an agreed small import check before a full CRM import. Mark “ready for review” separately from “approved for import”.
Make the columns understandable
Field definitions
Adapt these definitions to the brief. All examples below are illustrative.
- Source row IDs
Stable IDs of all input rows represented by an output record; merged records can have more than one.
Example: [ROW-014, ROW-027]
- Field name and type
Target column, meaning, format and allowed values. Record whether blank is valid.
Example: Phone · text · country code if known · blank allowed
- Duplicate rule
Explicit matching fields and normalization steps, with a separate policy for conflicting values.
Example: Review normalized email + company matches; retain conflicting values for review.
- Change record
Before, after, applied rule and source references; explain batch changes if not logging per cell.
Example: Location: “ bristol ” → “Bristol”; agreed whitespace/case rule.
- Issue and status
An unresolved value or conflict with an action owner; distinguish unknown from deliberately blank.
Example: Email missing · needs confirmation · do not invent a replacement.
- Readiness
Whether a file is ready for review or has actually been approved for the stated use.
Example: Ready for review; import approval outstanding.
Illustrative decision / not a client result
A decision in practice
Illustrative situation: two rows have the same normalized email and company but different job titles.
Action
Flag the pair as a possible duplicate and preserve both titles until the conflict policy is applied.
Reason
A matching key may identify the same contact, but it does not establish which title is current.
Follow-up
Ask the reviewer to select a supported value or leave it unresolved. Log the source IDs, rule, reviewer and decision date.
Acceptance checklist
Use these as review questions. A checkbox only means something after the work behind it has been checked.
- The original file is preserved and output records can be traced to source rows.
- The field dictionary matches the exported column names, formats and blank-value policy.
- Every merge or exclusion follows an agreed rule; unresolved issues have not disappeared from the handover.
- The row reconciliation balances, with the counting convention stated.
- The exported file has been reopened and checked in the intended tool; any import approval remains explicit.
What this does not establish
Cleaning improves organization and consistency; it does not verify that every supplied value is true or current. This checklist assumes one output record per retained group. Splitting records or combining datasets needs its own reconciliation rule. Import compatibility must be checked against the actual destination.
Try a related demonstration
Inspect the approach in an original interactive project. It uses synthetic data and is not a record of paid client work.
Data cleaning studioPut the brief to work
Need help with your next project?
Tell me the outcome, the source material and the constraints. Start with a description; keep sensitive records out of the enquiry form.
Discuss your project