What to Do Before Sending Data for Statistical Analysis
A practical checklist for cleaning and organising your master chart or dataset before analysis, so results are accurate and match your objectives.
Whether you analyse your data yourself or work with a statistician, the quality of the analysis depends on the quality of the dataset. A clean master chart saves days of back-and-forth and prevents errors that examiners notice. Use this checklist before you send or analyse your data.
1. Remove patient identifiers
Delete names, phone numbers, addresses and hospital registration numbers. Replace them with a study ID (001, 002, ...). Keep the key that links IDs to patients separately and securely, as your ethics approval requires.
2. One row per participant, one column per variable
Each participant should appear in exactly one row. Each column should hold one piece of information. Do not combine "BP 130/80" in one cell; use two columns, SBP and DBP.
3. Keep numbers as numbers
Write 12.4, not 12.4 g/dL. Put units in the column heading (Hb_gdL) or in a separate codebook. Text in a numerical column stops software from treating it as numerical.
4. Code categories consistently
Choose one coding scheme and use it everywhere, for example 1 = Male, 2 = Female. Avoid mixing "M", "male" and "Male" in the same column. Record the codes in a codebook.
5. Treat missing values deliberately
Leave a cell blank, or use one clearly defined code (such as 999) that cannot be a real value. Never use 0 for missing if 0 could be a real value. Count how much is missing per variable.
6. Check for impossible values
Sort each column or use summary statistics to find typing errors: an age of 230, a haemoglobin of 112 g/dL, a date after today. Correct them from the original forms.
7. Use one date format
Use a single format such as DD-MM-YYYY throughout, or split dates into separate day, month and year columns. Mixed formats are a common source of errors.
8. Keep raw values, not only categories
Record actual age, not only age groups. You can always create groups later, but you cannot recover exact values from groups.
9. Make sure every objective is covered
Go through your objectives one by one and confirm that the variables needed for each are present and complete.
10. Prepare a short codebook
| Column name | Description | Values / units |
|---|---|---|
| ID | Study ID | 001–360 |
| Age | Age in completed years | Number |
| Sex | Sex | 1 = Male, 2 = Female |
| Hb_gdL | Haemoglobin | g/dL |
| Anaemia | Hb < 11 g/dL | 0 = No, 1 = Yes |
Common mistakes
- Merged cells, colours or comments used to carry information.
- Several tables or summary rows on the same sheet as the data.
- Two rows for one patient (for example, baseline and follow-up) without a clear structure.
- Changing codes halfway through data entry.
Key takeaways
- Anonymise, then organise: one row per participant, one column per variable.
- Numbers only in numerical columns; consistent codes for categories.
- Define missing values and check for impossible ones.
- A one-page codebook makes analysis faster and safer.
References
- Broman KW, Woo KH. Data organization in spreadsheets. Am Stat. 2018;72(1):2–10. doi:10.1080/00031305.2017.1375989
- Indian Council of Medical Research. National Ethical Guidelines for Biomedical and Health Research Involving Human Participants. New Delhi: ICMR; 2017.
- Kirkwood BR, Sterne JAC. Essential Medical Statistics. 2nd ed. Oxford: Blackwell Science; 2003.
This article is for education. Always follow the requirements of your university, guide, ethics committee and target journal.