In this series of video tutorials, ICIJ reporter Kate Willson demonstrates four basic yet essential Excel functions to assist with data analysis during investigative reporting.
Want to see any other video tutorials about Computer-Assisted Reporting? Please let us know either in the comments below or at firstname.lastname@example.org.
Interested in viewing more of our investigative reporting video tutorials? Subscribe to ICIJ's YouTube channel to be the first to know when they are released.
We use Excel's "Auto Fill" function all the time when preparing data for analysis. When we first begin working with a table, we add our own unique ID - something as simple as 1, 2, 3, etc. But who has time to type upwards of 100,000 numbers? With the "Auto Fill" button it's simple. All you do is fill in the first two or three, highlight them and either double click or drag to continue the pattern down your column. We also use them, like you see in this tutorial, to add clarifying or clean names to confusing or dirty data.
Sorting and Filtering
Sorting and Filtering are great and easy ways to look at your data. Sorting allows you to group similar data together - you might want to copy and paste it into a fresh table to play with more closely, or you might simply need a quick count of all those rows. In other cases, you might chose to filter, which allows you to drill down deeper in the data. If you want a subgroup of a subgroup, for example. We use filter to crosscheck our data too, to make sure we have the number or rows we expect, or to identify how many black entries to consider.
Using Excel to join data from multiple cells is a powerful tool -- particularly if you're writing lengthy SQL queries. For example, if you want to filter records by more than one criteria, typing can be repetitive. By using the "Concatenate" function in Excel, you can automate the statements. This will not only save time, but it will make it less likely that you'll introduce mistakes into the query.
You don't always need a complex SQL query to analyze data. Excel's "Pivot Table" function can be powerful tool to run descriptive statics and show trends. It's also nice for those who prefer to interact with data visually.
Thanks for watching. If there are any other topics relating to Computer-Assisted Reporting which you would like to see covered, please let us know either in the comments below or at email@example.com.