Spreadsheets 101 and Beyond: Using Google Sheets

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.

Standardize how you deal with questionable data. When we structure data “things get lost along the way, including an understanding of what part of the information came from our primary source and what is our interpretation,” Singer-Vine said. Also, some data may simply be wrong or incomplete. You can handle that by adding a “notes” column, leaving the cell blank or using a question mark or N/A in the cell, Singer-Vine said. He advised against using highlighting or colors because Excel and Google Sheets don’t know how to interpret it. “If you wanted to know the difference between which cells were empty and which cells were empty and yellow, there’s no way really to do that. It can be useful for your own eye in sort of keeping track of your progress, but it will be very hard, virtually impossible, to take that information to account if you’re processing it in one of these tools or in Tableau or in a programming language,” he 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: Jeremy Singer-Vine, (Former) Data Editor, BuzzFeed News

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