By Jennifer Lu
Judging by the “oohs” and “ahhs” of appreciation from the audience, ProPublica reporters Ryann Grochowski Jones and Charles Ornstein reminded us why the humble Excel spreadsheet is still an excellent resource for data journalists. In this hands-on session, they covered how to use Excel for health care reporting.
To cover health care and Big Pharma, Grochowski Jones and Ornstein wrangled lengthy rows of data and made data searchable down to the granular level. How much money has your doctor received from drug companies? Check out ProPublica’s Dollars for Docs. How does your physician compare with other doctors in the number of tests and services they’ve billed to Medicare? Visit Treatment Tracker.
Grochowski Jones and Ornstein introduced three data sets that can be downloaded from the Centers for Medicare and Medicaid Services (CMS). These spreadsheets contain information such as nursing home violations and their severity, treatments that doctors billed to Medicare and the breakdown of medical services by race and geography.
Using PivotTables and filters, we practiced searching for dysfunctional nursing homes in our home states. We figured out which doctors in a particular specialty are prescribing the most treatments per number of patients they see.
Watching the worst nursing homes in your area rise to the top with just several clicks of a button is impressive, though slightly disturbing.
“Did we just blow your mind?” Grochowski Jones asked a crowd of impressed journalists.
Here are some of their tips to make data analysis in Excel easier:
While working through a hiccup, Grochowski Jones and Ornstein discovered a new tip from an audience member. Instead of making a brand new PivotTable to reflect the changes you made in your primary spreadsheet, you can refresh your current PivotTable using the “Refresh” button in the pink “Options” tab under “PivotTable Tools.”
“Awesome,” Ornstein said. “We’re learning new things every day.”
“We share, you share,” Grochowski Jones said. “That’s what the NICAR community does every day.”
Jennifer Lu is studying investigative and data journalism at the Missouri School of Journalism.