How do you clean survey data for M&E? A plain-English routine
Oct 12 2026 · Templates
A repeatable, documented routine for cleaning survey data in M&E, from raw export to analysis-ready file.
Cleaning survey data means turning a raw export into an analysis-ready file through a fixed, documented sequence. Start by freezing an untouched raw copy. Then standardise text, dates and categories; resolve duplicates; recode missing values separately from zeros; check ranges and logic; and log every change. The most common error is treating a blank as a zero, which deflates averages and hides non-response. Keep the raw file untouched so any step can be reversed, and version the cleaned file with a date. When a donor asks how a figure was derived, the cleaning log is your evidence. The routine is repeatable and auditable.
What is the first step in cleaning survey data?
Freeze an untouched raw copy before you change anything. Store it read-only, with the original file name and export date. Every later step should be reversible from this master file. If you edit the raw file directly, you lose the ability to check what changed and why.
Treat the raw file as evidence. Work on a copy, and never overwrite the original.
How should you standardise text and dates?
Standardise text to a single case and trim stray spaces. Use one date format, ideally ISO 8601 (YYYY-MM-DD). Map category labels to a controlled list so that 'Yes', 'yes' and 'Y' become one value. Keep a mapping table for any recoding.
How do you handle duplicates in survey data?
Identify a unique record ID. Check for exact duplicates first. Then look for near duplicates: same respondent, different spelling or date. Decide a rule: keep first, keep most complete, or merge. Document the rule and the number of records removed.
What is the difference between missing and zero?
| Value | Meaning | Action | Risk if confused |
|---|---|---|---|
| Blank | No answer recorded | Code as missing | Deflates averages if treated as zero |
| Zero | A true zero value | Keep as zero | Inflates averages if treated as missing |
| Refused | Respondent declined | Code as refused | Loses non-response pattern |
| Don't know | Respondent unsure | Code separately | Mixes uncertainty with zero |
Use distinct codes for missing, refused and don't know. Never let a blank become a zero by default.
Which checks catch most data errors?
- Range checks: values outside plausible minimum and maximum.
- Logic checks: age cannot be negative; end date after start date.
- Skip patterns: questions that should be blank are blank.
- Outliers: flag extreme values for review, not automatic deletion.
- Cross-tab consistency: totals match across related questions.
Why document every cleaning change?
A cleaning log records what changed, why, when and by whom. It makes your work auditable and repeatable. When a donor or reviewer asks how a figure was derived, the log is the answer. Version the cleaned file with a date and keep the log beside it.
If you cannot undo a cleaning step, you have lost the record.
What does a practical cleaning checklist look like?
- Freeze an untouched raw copy.
- Standardise text, dates and categories.
- Resolve exact and near duplicates.
- Recode missing, refused and don't know separately from zero.
- Run range, logic, skip and outlier checks.
- Document every change in a cleaning log.
- Version the cleaned file with a date.
- Back up both raw and cleaned files.
How do you make cleaning repeatable?
Write the routine as a script or a step-by-step template. Use consistent file names and folder structure. If you clean the same survey each round, reuse the same checks and log format. This saves time and reduces mistakes.
Where can you get a ready-made cleaning workflow?
If you want a pre-built workflow, the Kobo Toolkit covers data export, cleaning and reporting. You can also browse M&E tools for templates and checklists. For ongoing support, membership gives you access to a library of resources.
Written for M&E professionals. Not a guarantee of accuracy — always verify every claim against its source, and let a named human sign off. Get the free handbook.