John Burn-Murdoch, data journalist for the Financial Times, used the mid-afternoon session of the news:rewired conference, today in London, to provided a step-by-step masterclass on the crucial skills needed to extract stories from in spreadsheets using Google Docs.
Burn-Murdoch detailed five key techniques that can make spreadsheets your best friend when attempting to interpret data in Google Docs.
Filtering
Filtering and sorting fields in a spreadsheet allows you to narrow down your data to a pre-determined set of values, meaning that the data left is much ‘lighter’ and clearer, he said.
Simpler dataset are easier to interpret and it allow you to be more focussed on the data you wish to work with, he added.
Once you have filtered all of your data into the fields you are specifically looking for, he added, you can try more in depth methods to extrapolate interesting ‘taking points’ from your data – these are the bits that will appeal to audiences.
Pivot tables
Pivot tables group your data in common values. You can ‘ask your data specific questions’ and create a pivot table to measure different fields against each other.
Burn-Murdoch said pivot tables can take thousand rows of data and strip out what you think your key news lines will be. Specific calculations can be performed with pivot tables, he added, to find out different proportions or percentages from the data, as well as calculating more ‘reader friendly’ information such as average expenditures.
Formulas
Burn-Murdoch delved deeper into ‘maths geek’ territory and explained the importance of using formulas in spreadsheets to devise more meaningful interpretations of numerical data.
Formulas can be used in conjunction with pivot tables to calculate specific calculations that can then be sorted in order to make data ‘more meaningful and easier to package for the reader’.
Formulas such as ‘Rank’ formulae allows you to rank certain fields you may have calculated against data you have sorted in a pivot table to extract more data which is more easier to digest for the reader.
Vertical lookup
This tool is used for combining two different databases that have a common value (e.g. a country). By combining certain fields across different databases you can generate even more unique talking points to stories by adding in an extra element.
For example, you may want to measure GDP per capita for a country against visits to the country, where the common value for each spreadsheet would be the country.
Burn-Murdoch warns that when looking at different datasets regarding countries, vertical lookups can prove problematic as some countries may be categorised differently – for example, one spreadsheet may categorise China, while others may be Hong Kong and China, meaning you will have be careful when you merge certain fields.
Correl
The Correl feature of Google spreadsheets measures correlation against two different values. This would be useful if you wish to measure to see if there is a positive or negative relationship between values, such as visits to a country and GDP per capita.
For more a more step-by–step rundown of John Burn-Murdoch’s tips and tricks you can look at the live blog of his talk.