Data Literacy for Reporters: A Crash Course in Excel and More

Matthew Kauffman, an investigative reporter for the Hartford Courant, started a two-part, data literacy workshop for journalists with a question: “How many people got into journalism primarily because they were hoping to do more math?”

When zero hands went flying into the air, he was not surprised.

“There’s just kind of a disconnect between what we do and numbers and math,” Kauffman said. However, he argued it is more important than ever for reporters to get comfortable with math.

The ‘Big Data’ Era

“We are sort of in this era of big data, and nowhere is that more prevalent than in education,” Kauffman said during the May 17 workshop, part of EWA’s 2018 National Seminar.

On the education beat, examples include standardized test scores, graduation rates, school discipline referrals, absenteeism data, and student loan defaults, to name just a few.

“We are just absolutely awash in data and developing some mastery with data can kind of give you an edge,” he said. “It can help you find better stories. It can help make the stories you find better. ”

Kauffman, a self-described “data nerd,” spent the next two-and-a-half hours helping a lecture room full of reporters get more comfortable with raw numbers, data visualization and, primarily, using Excel as a tool for data analysis and finding news stories in datasets.

The Secrets of Spreadsheets

A key tip for reporters with arithmophobia was that Excel will do the math for you – you just have to learn how to use it. From there, Kauffman went on to shine a light on what had been, for at least some of us, the shadowy secrets of spreadsheets.

The first lesson was how to apply basic arithmetic to spreadsheet cells. In Excel, formulas all begin with an equal sign. For example, start a formula with “=SUM” and highlight a range of cells to have Excel total the values. Once you learn how to plug in the correct formula, Excel does the math for you!

Next, Kauffman explained how to use basic math to find stories in a dataset. (Note: He included a link to the sample data in a tipsheet he produced for this session).

Click here to download Kauffman’s tip sheet!

Using a sample town budget, Kauffman demonstrated how to break down how much the budget was increasing for each of his fictional town’s departments. This involved entering basic formulas into Excel to calculate the difference in funding from one year to the next, the percent change, and the percent of the whole.

Sorting and filtering the columns helped reveal the winners and losers in the budgets and raised other questions that, in a real-world scenario, journalists could ask sources, such as: Why are the town hall department budgets, like accounting and financing, all going up while the public school and teen center budgets are going down?

Beware Unintended Calculations

But, Kauffman cautioned, it’s important to keep an eye out for unintended calculations.

He demonstrated how Excel attempts to anticipate what a user is trying to do, but it’s not right all the time. If either you or Excel make a mistake, he said, there is an easily accessible “undo” button (or type CTRL + Z on a PC).

Data can also be filtered in Excel to show only the information with certain characteristics. Using a spreadsheet of test scores, we learned to filter for only the schools with the top ten test scores across the board, or the bottom 15 schools in math testing. Thanks to an audience question, we also learned how to have the spreadsheet rank the schools for us.

Another key Excel skill Kauffman illuminated was how to create pivot tables — a tool to summarize data and dig deeper into a data set. He used a sample spreadsheet that showed a school district’s vendor payouts over a fiscal year. He demonstrated how to create a pivot table to isolate one vendor, to see how much it was paid throughout the year, and how to break down that information by month.

The next lesson was on “IF statements,” which involve asking a yes/no question of data. Using fictional basketball scores, Kauffman showed how to have Excel automatically fill in if a team won or lost each game.

This involved creating an Excel formula that asked, in simpler terms, “If the score for Team A is higher than Team B, then put a “W” in the formula column. If not, put an “L” in the formula column. Then we took the next step of learning how to tell Excel to fill in a “T” for a tie.

Kauffman’s tipsheet includes an example using IF statements on the education beat with test data. Write a formula that prints “higher” or “lower” after comparing the value in one cell — a score from one year — to another. The results provide a helpful visual reminder.

Tapping Visual Tools to Convey Data

Sometimes the best way to convey data in a story is by creating a visual. To that end, Kauffman introduced Google Fusion Tables.

He used that tool to create an intensity map by merging his spreadsheet of data on per capita state income with Google’s U.S. map. The result was a map that showed states in different colors, depending on per capita income range. Kauffman said Google Fusion Tables are “easy to work with” and “a nice way of representing data.”

Kauffman also said he’s always happy to help data journalists, so feel free to reach out to him if you need some guidance from someone who’s both a data nerd and able to break it down for those who may be data challenged. Email him at mkauffman@courant.com.