Use basic Excel tricks to explore health care data

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:

• Always keep an original copy of your data separate from your working file. Your future self will thank you.
• Want to group information using multiple categories using a PivotTable? Drop your fields of interest into the “row labels” quadrant hierarchically. For example, put the “state” field above the “city” field to sort by state, then city.
• Use “Freeze Panes” under “View” to lock in place row and column labels that you want to keep visible at all times, regardless of how far right or down you scroll.
• PivotTables allow you to count how often a value in one field (severe violations) appears in relation to another field (like a particular nursing home in your city). Click on the tabulated number to produce a third spreadsheet that displays a description of each severe violation at that nursing home.

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.