Excel

Question

Can I take a real public health dataset and build an interactive Excel dashboard that lets someone look up any hospital in the country and see how it stacks up against state and national averages?

Hypothesis

I figured the data would show big regional differences in hospital quality and that ownership type would have a real effect on average ratings. I wanted to see if Veterans Health or non profit hospitals would outperform for profit ones.

Approach

Pulled CMS Hospital General Information data covering 5,432 hospitals across the country. Cleaned the data down to 19 useful columns and built a calculated Performance Score using the count of mortality, safety, and readmission measures where each hospital beat or trailed national comparisons. Set up a Hospital Lookup sheet with a dropdown of every hospital name, then used XLOOKUP to pull the full profile and AVERAGEIF to calculate state level benchmarks on the fly. Added conditional formatting so the comparison column lights up green when a hospital beats national averages and red when it falls below. Built a separate Performance Dashboard with four pivot tables (top hospitals, performance by state, ratings by state, performance by ownership type), KPI cards, and three slicers so the user can filter the entire dashboard by state, ownership, or hospital type.

Result

Veterans Health Administration hospitals came out on top with the highest average star rating (4.16), beating non profit and government hospitals across the board. Proprietary (for profit) hospitals had the lowest average rating at 2.80. Utah had both the highest average star rating and the highest performance score of any state, which surprised me. The dashboard also exposed something I would not have caught without the comparison view. Many hospitals that look strong on raw star rating actually trail on the more granular measure based scores, and the reverse is true too. Looking at multiple metrics together tells a stronger story than any one number on its own.