ACADEMIC WRITING GUIDE
How to Clean Research Data Before Analysis (With Worked Examples)
You have completed your questionnaire or finished entering research records into a spreadsheet. The numbers are there, but you notice blanks, inconsistent labels, an impossible age and two nearly identical responses. Can you start calculating averages and testing hypotheses? Not yet. First you need a transparent way to decide what is an error, what is a legitimate observation and what uncertainty remains.
Data cleaning is the process of inspecting, documenting and, where justified, correcting data-quality problems before analysis. It is not a licence to remove inconvenient results. The goal is to produce an analysable dataset without changing the evidence to fit your expectations.
This guide shows a practical research-data cleaning workflow, fully worked survey examples, responsible treatment of missing values and outliers, and how to describe your decisions in a dissertation.
What counts as research data cleaning?
Typical tasks include detecting duplicates, verifying valid ranges, standardising inconsistent categories, checking dates and units, investigating missing values, applying a questionnaire scoring key, and checking for direct identifiers in files intended for sharing. Some flagged entries are correct and must be retained. Others are genuine mistakes that should be corrected from trustworthy source records or marked unresolved.
For example, an age of 650 in a study of university students is likely invalid; an unusually high but eligible age of 64 may be perfectly legitimate. Cleaning begins with investigation, not automatic deletion.
If you are still planning how responses will be collected, begin with our data collection procedure guide; prevention through clear forms and validation is easier than retrospective repair.
Data cleaning, preparation and analysis: what is different?
| Stage | Main question | Example |
|---|---|---|
| Cleaning | Are records accurate, consistent and sufficiently complete? | Verify duplicate IDs, invalid dates and coding errors |
| Preparation | Are variables structured and scored for the intended analysis? | Create a composite scale score with documented rules |
| Analysis | What do the cleaned data say about the research question? | Estimate an association and uncertainty interval |
These stages overlap. For instance, reverse scoring is preparation, but checking that reverse scoring was applied correctly is also a data-quality check. What matters is keeping an audit trail of decisions made before interpreting results.
Before touching the dataset: protect the original
Save the untouched export as a read-only raw file. Create a separate working copy and assign a clear version name, such as survey_clean_v01. Keep any participant identifiers or contact details in access-controlled files separate from analysis data wherever feasible. Use a codebook describing every field, its meaning, allowed values, missing-value codes and intended scoring. Never overwrite the raw source.
Set a reproducible rule for each planned check. For example: eligible ages must be between 18 and 65 according to the approved sampling frame; satisfaction items use integers 1–5; a composite score requires at least four of five items. These are illustrative rules and must be adjusted to the study protocol and instrument.
For guidance on documenting what each variable represents and how it will be measured, see our operational definitions guide.
A nine-step research data cleaning workflow
Step 1: Establish the unit of analysis and row identifiers
Decide what one row represents: one participant, one visit, one classroom or one survey submission. A repeated participant ID is not necessarily a duplicate if the study legitimately records multiple visits. Compare IDs, survey timestamps and relevant source records; follow your consent and data-retention rules before removing anything.
Step 2: Inspect the structure and codebook
List every column, type and allowed range. Check that numeric fields are genuinely numeric; dates use an unambiguous format; category values share one approved coding system; and columns do not mix units. For example, a duration recorded as both “30 minutes” and “0.5 hours” needs conversion to a common unit with the original retained.
Step 3: Make a validation and frequency report
For each variable, count valid, blank and out-of-range observations. Produce frequency tables for categorical fields, ranges and plots for numeric ones, and a cross-variable list of contradictions (for example, an interview date before the recorded consent date). A flagged record is a request for review, not proof of misconduct or error.
Step 4: Investigate impossible and inconsistent values
If a respondent marked satisfaction as 7 on a 1–5 item, check whether the export or entry process changed the coding. Correct only when the original form or another authoritative source supports the correction. If no reliable explanation exists, retain the raw value in the source file and classify the analysis value as invalid/missing with a reason code.
Step 5: Resolve genuine duplicates without deleting legitimate repeats
Identify exact duplicate submissions and probable duplicates separately. An exact duplicate created by a spreadsheet import may be removed after confirming provenance; two different people who chose the same answers are not duplicates. For repeat-response studies, apply a predeclared retention rule (for example, one complete eligible submission per participant), and record both the rule and exclusions.
Step 6: Examine missing data systematically
Distinguish skipped questions, structurally inapplicable questions, withdrawal, technical failures and invalidated entries. Calculate missingness for each variable and participant, and compare characteristics of complete versus incomplete records when feasible. Do not replace every blank with zero: zero may be a genuine measurement, whereas “not answered” is unknown.
The response to missing values depends on the design, mechanism, analysis method and extent of missingness. Complete-case analysis can lose information or introduce bias. Single mean substitution can distort relationships and understate uncertainty. Multiple imputation or model-based strategies require justified assumptions and suitable expertise; they are not automatic fixes for a student dataset.
Step 7: Inspect outliers and influential observations
Use histograms, boxplots, scatterplots and summary statistics to flag extreme values. Then ask whether each observation is a data-entry mistake, a unit mismatch, an eligible extreme case or a different population. Never delete a real value simply because it weakens a hypothesis. If a defensible extreme value affects results, report robust summaries, justified transformations or sensitivity analyses as appropriate.
Step 8: Check scoring, skips and derived variables
Revisit the scoring instructions for every instrument. For a 1–5 negatively worded item, a reversed score can be calculated as 6 minus the original item response—but only if that instrument actually specifies reverse coding. Validate the resulting range, treatment of missing items and whether the composite is a sum or mean. Also check skip patterns and derived variables against their source fields.
A reliable survey starts with well-defined response options and skip logic; our questionnaire design guide explains those upstream choices.
Step 9: Freeze an analysis-ready file and document the decisions
Export a cleaned, versioned dataset plus a cleaning log containing the variable, issue, affected rows, investigation, rule, decision, date and responsible reviewer. Keep a reproducible script where practical. Write a brief quality summary: initial records, exclusions with reasons, missingness, corrections, retained unusual values and final eligible sample. Use the same frozen version for analysis and reporting.
Worked example 1: Cleaning a small student survey dataset
Hypothetical study: relationship between perceived written-feedback usefulness and academic engagement among eligible undergraduates. The approved sample is students aged 18–65; usefulness is scored 1–5. Each row should represent one eligible participant’s final submission.
| ID | Age | Usefulness | Engagement | What the raw file shows |
|---|---|---|---|---|
| S01 | 20 | 4 | 3 | Valid record |
| S02 | 650 | 5 | 4 | Impossible age |
| S03 | 21 | 7 | 4 | Out-of-range usefulness |
| S03 | 21 | 7 | 4 | Exact duplicate import row |
| S04 | 22 | blank | 2 | Unanswered item |
| S05 | 64 | 1 | 5 | Unusual age but still eligible |
Start with six imported rows, but only five unique participant IDs. Check the source/export log: suppose it confirms the second S03 row is an accidental duplicate generated by an import. Removing only that duplicate leaves five unique records. Do not discard S05 because age 64 is uncommon among undergraduates; it satisfies the prespecified eligibility range.
Suppose the original response for S02 is 65 and the data-entry log proves 650 was a typing error. Correct the analysis copy to 65 and log the evidence. Suppose the original form for S03 genuinely records 7 despite the permitted 1–5 range. It is invalid for that item: mark the analysis value missing and record why, without inventing a replacement. S04 remains missing because no response is available.
After these verified decisions there are five eligible unique participants, three valid usefulness responses (S01, S02 and S05) and two missing/invalid usefulness responses (S03 and S04). Missingness for that item is 2/5 = 40%. For this tiny example, that is substantial: it should be disclosed and the researcher should not claim a strong association based on three paired observations. A real study would revisit the analysis plan, reasons for missingness and appropriate methods.
Illustrative cleaning log: duplicate S03 import row → removed with export evidence; S02 age 650 → corrected to 65 using source; S03 usefulness 7 → recoded missing as invalid; S04 usefulness blank → preserved as missing; S05 age 64 → retained. Notice how each decision differs: correction, exclusion, missing-value coding and retention are not interchangeable actions.
Worked example 2: Reverse coding without inflating a scale
Suppose a hypothetical engagement scale uses three items scored 1 (strongly disagree) through 5 (strongly agree). Item E2 is negatively worded, and the approved scoring guide explicitly requires reversal. A participant responds E1=4, E2=2 and E3=5.
Reverse E2 with 6 − 2 = 4. The correctly scored mean is (4 + 4 + 5) / 3 = 4.33. If a researcher forgets the reversal, the mean becomes (4 + 2 + 5) / 3 = 3.67. That discrepancy changes the derived result even though none of the original answers is erroneous.
Quality check: confirm that only the designated negative item is reversed, that all transformed values fall between 1 and 5, and that missing responses are handled according to the actual instrument guidance. The examples are illustrative; do not adopt this scoring formula for a published instrument without checking its documentation.
Worked example 3: A clinic appointment dataset with competing explanations
Hypothetical study: describing missed outpatient appointments during a defined three-month period. One row represents one scheduled appointment—not necessarily one patient. The export has patient IDs, clinic codes, booking dates, appointment dates and attendance status. A patient with two legitimately scheduled visits should retain two rows; treating identical patient IDs as duplicates would erase real appointments.
First, check that appointment dates are within the study window, that booking dates do not exceed appointment dates unless the system permits retrospectively entered bookings, and that attendance status uses a consistent codebook. For a blank attendance status, check the appointment system rather than guessing “missed.” If a clinic code is misspelled but the source dictionary confirms the intended clinic, standardise it while retaining the original value in the audit trail.
Next, flag cases where the same appointment ID appears twice. Confirm whether the second record is a true export duplicate or a legitimate reschedule record with its own event status. Decide in advance whether the analysis counts original appointments, final bookings or completed encounters. Those choices define the denominator and can change the missed-appointment rate.
Do not confuse clean administrative data with a complete study population. Records may omit visits at outside clinics or people who never scheduled care. Such coverage limits must be reported even when every retained cell is internally consistent.
For a clear account of how exclusions and coverage constraints affect interpretation, see our limitations of a study guide.
A practical decision table for common data-quality flags
| Flag | Investigate first | Defensible response |
|---|---|---|
| Age outside eligibility | Original form, export mapping, eligibility rule | Correct with evidence; otherwise flag/exclude according to protocol |
| Blank response | Skip logic, item applicability, respondent profile | Code missingness explicitly; choose justified analysis handling |
| Repeated ID | Unit of analysis, timestamps, multiple visits | Retain legitimate repeats; remove confirmed import duplicates |
| Extreme value | Source accuracy, units, population | Retain valid extremes; assess influence transparently |
| Inconsistent label | Codebook and original entry | Standardise known equivalents; preserve original record |
| Unexpected scale score | Instrument key and item transformations | Recalculate correctly and document scoring rule |
How to write the data-cleaning subsection in your dissertation
A methods chapter should describe planned quality controls; a completed results or methods report should state what was actually done. Do not write “all outliers were removed” if none were investigated. A useful paragraph names the software or procedure, types of checks, criteria for corrections and exclusions, missing-data approach, scoring checks and final sample.
Illustrative completed-study paragraph
“The exported survey data were retained in an unchanged source file and screened in a separate analysis copy. Checks covered participant identifiers, eligibility ranges, response coding, duplicate submissions, missing items and scoring consistency. Duplicate rows were removed only when source records confirmed repeated imports. Out-of-range entries were checked against original forms; entries without a defensible correction were marked missing for the affected variable. The scoring key was applied to designated reverse-worded items and verified against allowable values. A cleaning log recorded every correction and exclusion. The analysis dataset was frozen after these checks, and item-level missingness and the final eligible sample were reported.”
This is a structural model, not a factual account of a completed study. Replace general phrases with your exact rules, observed counts, software and decisions. If the dataset is small, report concrete numbers; if methods changed after inspecting results, disclose that change.
Data cleaning in Excel, SPSS, R or Python: which should you use?
The tool matters less than traceability. In Excel, filters, data validation and pivot tables can reveal problems, but manual edits need disciplined logging and protected raw copies. In SPSS, frequency tables, missing-value definitions and transformation syntax can help reproduce checks. In R or Python, scripts can enforce ranges and keep version-controlled transformations. Choose tools you can operate correctly and explain clearly; do not promise reproducibility if all cleaning consisted of undocumented manual clicking.
For small studies, an Excel cleaning log can be adequate. For larger datasets or repeated imports, a script that reads raw files and writes a new cleaned file is safer because the steps can be rerun. Data protection requirements still apply regardless of software.
Once your documented cleaning decisions are complete, move to our quantitative data analysis guide for choosing analyses that fit the variables and research question.
Common mistakes to avoid
-
Deleting every outlier without investigating whether it is real.
-
Replacing every blank with zero, even where zero has a valid meaning.
-
Removing repeated participant IDs in a dataset where a participant can have multiple visits.
-
Changing original survey entries without a record of the change and supporting evidence.
-
Using a convenient mean replacement without considering bias and uncertainty.
-
Reverse coding an item because it sounds negative without checking the published scoring key.
-
Cleaning until a preferred statistical result appears.
-
Reporting only the final sample while concealing corrections, exclusions and missingness.
-
Sharing direct identifiers or raw confidential files outside approved access arrangements.
Frequently asked questions
Should I remove every row with a missing value?
No. Some analyses can use partially complete cases, and deleting all incomplete records may reduce sample size or bias estimates. Assess the pattern and mechanism of missingness before choosing a justified approach.
Is an outlier automatically an error?
No. An outlier is a value that stands apart from others. It may be an error, a valid extreme case or evidence of an important subgroup. Investigate first and report any sensitivity analyses.
Can I clean qualitative interview data?
Yes, but the meaning is different. Check transcript completeness, speaker labels, redactions, transcription errors and consistency between audio and text. Do not “clean away” uncomfortable participant statements. Analytical coding and interpretation occur later.
Do I need to describe cleaning if I found no problems?
Yes, briefly. State which checks were completed and that no corrections or exclusions were required, if that is what actually happened.
Can I use AI to automatically fix my dataset?
Automated tools can flag patterns, suggest checks or help write reproducible code, but they should not make unreviewed decisions about deleting participants, changing responses or disclosing sensitive records. Verify every consequential change against the protocol, evidence and privacy requirements.
Final pre-analysis checklist
-
The raw dataset has been preserved separately.
-
The unit of analysis and eligibility criteria are explicit.
-
A codebook lists permitted values, units and missing codes.
-
Duplicates and repeated observations have been distinguished.
-
Invalid values have been investigated, not silently replaced.
-
Missingness has been counted and its treatment justified.
-
Outliers have been reviewed without outcome-driven deletion.
-
Reverse-scoring and derived-variable rules have been checked.
-
Privacy-sensitive fields have been handled appropriately.
-
Every correction and exclusion is documented in a cleaning log.
-
The final sample and relevant data-quality limitations are reportable.
-
The analysis-ready file is versioned and reproducible where practical.
Final takeaway
Good research data cleaning does not make results look better; it makes the path from raw evidence to reported results more defensible. Protect the source, define checks in advance, investigate abnormalities, document decisions and report unresolved uncertainty. A modest, transparent dataset is more credible than a polished spreadsheet whose changes cannot be explained.
If you need help organising a research brief, planning an appropriate methodology or improving the clarity of a dissertation chapter, explore Acadelyra’s student support options. Use any support in line with your institution’s academic-integrity requirements; the research decisions and claims must remain yours.
References and further reading
-
UK Data Service. QAMyData: numeric-data quality assessment (missingness, duplicates, outliers and identifiers). https://ukdataservice.ac.uk/about/research-and-development/past-projects/qamydata/
-
Network of the National Library of Medicine. Data Cleaning glossary. https://www.nnlm.gov/resources/data/data-glossary/data-cleaning
-
Van den Broeck, J., Cunningham, S. A., Eeckels, R., & Herbst, K. (2005). Data cleaning: Detecting, diagnosing, and editing data abnormalities. PLOS Medicine. https://pmc.ncbi.nlm.nih.gov/articles/PMC1198040/
-
Sharifnia, A. M., et al. (2026). A primer of data cleaning in quantitative research: Handling missing values and outliers. Journal of Advanced Nursing. https://pubmed.ncbi.nlm.nih.gov/40145308/
Editorial note: All numerical records and research scenarios are invented for teaching. Consult your supervisor, instrument documentation, study protocol, institutional ethics procedures and an appropriately qualified statistician where necessary.