5 takeaways:
➀ 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.
➂ 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.
➄ VLOOKUPS and pivot tables allow for more analysis. To search for a specific value and return information related to that value – for instance, a customer name and a phone number – use the VLOOKUP function, Schechter advised. Starting in a new cell with an equal sign, the way you do for other formulas, start typing VLOOKUP. Then put in the parameters based on what you want to search (i.e. phone numbers) and what values you want to index (i.e. customer names). Schechter noted that some people prefer to do an index match rather than VLOOKUP. He also uses pivot tables. “Pivot tables are great because they all are very quick ways to analyze your data, to change parameters, without having to do all these different formulas,” Schechter said. Click any cell, go to the insert tab, and click on pivot table. After it creates a new sheet, you’ll see an interface on the right to create the pivot table. You’ll have the option of dragging and dropping any header from your data table into the pivot table for analysis.
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. For Google Sheets, watch Jeremy Singer-Vine’s presentation.
Speaker:
Devin Schechter, Data Science and Analytics Associate Manager, Accenture Federal Services
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.
