Guided Notes

Cleaning Healthcare Data in Excel

Spot and fix the messy data analysts meet daily — inconsistent text, codes stored as numbers, duplicates, blanks, impossible values — with TRIM, UPPER, PROPER, LEFT/RIGHT and IF.

Real data is messy

Slide 1 / 5

Typical problems in a healthcare extract: • Inconsistent text: 'Cardiology', 'cardiology ', 'CARDIOLOGY'. • Codes losing leading zeros: NDC '00093…' becomes 93…; ZIP '02134' becomes 2134. • Duplicates: the same visit exported twice. • Blanks and placeholders: '', 'N/A', 'unknown', 999. • Impossible values: age 212, discharge before admission, weight 0. • Mixed units: pounds and kilograms in one column. Cleaning often takes more time than analysis — and it's where accuracy is won.