Microsoft Excel for Biostatistics and Health Data Analysis
Microsoft Excel is a spreadsheet application used to enter, clean, summarise, analyse and visualise health data. It is useful for small and medium datasets when good data-management practices are followed.
Learning objectives
- Structure a reliable health-data worksheet.
- Use formulas and functions for descriptive analysis.
- Clean, validate, sort and filter records.
- Create appropriate tables and charts while protecting confidentiality.
Designing the dataset
- One row represents one patient, participant or event.
- One column represents one variable.
- Use a unique study ID; avoid names where not required.
- Keep a data dictionary defining variable names, labels, units and codes.
- Use consistent codes, for example 1 = male, 2 = female, 9 = missing, and document them.
- Store raw data separately and analyse a protected working copy.
Useful functions
SUM, AVERAGE, MEDIAN, MODE.SNGL, MIN, MAX, COUNT, COUNTA, COUNTIF, SUMIF and STDEV.S.
Logical and lookup tools
IF, AND, OR, IFERROR, XLOOKUP/VLOOKUP, conditional formatting and data validation.
Common calculations
| Task | Example formula |
|---|---|
| Mean values in B2:B101 | =AVERAGE(B2:B101) |
| Median | =MEDIAN(B2:B101) |
| Sample standard deviation | =STDEV.S(B2:B101) |
| Count “Positive” | =COUNTIF(C2:C101,"Positive") |
| Percentage positive | =COUNTIF(C2:C101,"Positive")/COUNTA(C2:C101)*100 |
| Age group | =IF(B2<5,"Under 5","5 years or older") |
Data cleaning workflow
- Check completeness and duplicates.
- Standardise dates, categories and units.
- Use filters to inspect impossible values.
- Apply validation limits, such as age 0–120.
- Compare summaries with source documents.
- Document every correction in a cleaning log.
PivotTables
PivotTables quickly summarise counts, totals, averages and percentages by categories such as sex, facility or diagnosis. Place the grouping variable in Rows, outcome in Values, and optional strata in Columns or Filters. Verify that Excel uses the intended calculation rather than defaulting incorrectly to Count or Sum.
Charts for health data
- Bar chart: compare categories.
- Histogram: show a continuous distribution.
- Line chart: display trends over time.
- Scatter plot: explore two numerical variables.
- Pie chart: use sparingly for a few parts of a whole.
Quality and safety
Excel can silently convert dates and codes, formulas can be overwritten, and copied rows may contain hidden errors. Use versioned backups, locked formula cells, reproducible formula columns and regular validation. Remove direct identifiers and follow institutional privacy rules.
Practice exercise
- Create ten fictional patient records with age, sex, diagnosis and outcome.
- Calculate mean and median age.
- Use COUNTIF to calculate outcome proportions.
- Create a PivotTable and a bar chart.
Return to the Clinical Medicine Year 2 curriculum
References
- Microsoft Support: Excel functions, tables and PivotTables.
- WHO. Data Quality Review Toolkit.
Educational content only; identifiable patient data must be handled under applicable privacy and institutional policies.
