Spreadsheets 101 and Beyond: Using Microsoft Excel

How to use Excel and Google Sheets to structure your data. Jeremy Singer-Vine and Devin Schechter provide step-by-step instructions for data journalists on spreadsheet formatting and tools.
by Anne Godlasky, National Press Foundation

First things first: Clean up your data. Formatting a new dataset makes it easier to read and less likely to be misinterpreted. First, “select all,” hover between columns and double click to reformat your columns to fit the data within them. Then do the same for rows, said Devin Schechter, an Excel whiz for Accenture Federal Services. Some more basics: Use separate cells for first and last names and standardize dates, suggested Jeremy Singer-Vine, former data editor for Buzzfeed News and author of the Data Is Plural newsletter. “Depending on what country you live in, the date [08-03-46] may be interpreted as August 3rd, 1946, or March 8th, 1946,” Singer-Vine said. “The YYYY/MM/DD format for dates removes all possible ambiguity.” You can also use the proper command to capitalize the first letter of the names, said Schechter, and the “remove duplicates” command to clear redundant entries.

Basic Excel functions can save you time. Check the formulas tab any time there’s a complicated operation you want to try, Schechter said. “It’s highly likely that Excel has already built that out for you, so you don’t have to spend 20 minutes figuring out how to do your multiplication, addition, subtraction across all these different cells.” Excel can be a simple calculator and can also do s averages, remainders, interest percentages and other functions. The other benefit of using Excel for these formulas is that if you enter an update, you needn’t manually re-do your work. “If I had just typed the numbers in manually, they can’t change, they’re not adaptable. That’s one of the biggest perks of Excel is that as you change your inputs, as you add and subtract data, it will automatically continue that with you,” Schechter said.

Filter to hide data without deleting it. Filtering your data can allow you to zero in on the information most pertinent to your reporting. Right click on any cell and select the filter icon and filter dropdowns will appear on your headers. “I will no longer have those unchecked values visible in my data set. Again, it doesn’t mean they’re not there,” they will reappear when you clear the filter, Schechter said.

Note: These were hands-on workshops that cannot adequately be summarized. NPF strongly encourages those who want a better understanding of Excel to watch Devin Schechter’s presentation.

Speaker: Devin Schechter, Data Science and Analytics Associate Manager, Accenture Federal Services

This program was funded by the Evelyn Y. Davis Foundation. NPF is solely responsible for the content.