Tag Archives: vlookup

I’ve updated the Inverted Pyramid of Data Journalism — and brought together resources for every stage

Leave a reply
Inverted pyramid of data journalism: conceive, compile, clean, context, combine (with 'question' throughout). Communicate: vis, narrate, humanise, personalise, socialise, utilise

It’s over a decade since I published the Inverted Pyramid of Data Journalism. The model has been translated into multiple languages, taught all over the world, and included in a number of books and research papers. But in that time the model has also developed and changed through discussion and teaching, so here’s a round up of everything I’ve written or recommended on the different stages — along with a revised model in English (shown above; versions have been published before in German, Spanish, Finnish, Russian and Ukrainian).

The most basic change to the Inverted Pyramid of Data Journalism is the recognition of a stage that precedes all others — idea generation — labelled ‘Conceive’ in the diagram above.

This is often a major stumbling block to people starting out with data journalism, and I’ve written a lot about it in recent years (see below for a full list).

The second major change is to make questioning more explicit as a process that (should) take place through all stages — not just in data analysis but in the way we question our sources, our ideas, and the reliability of the data itself.

A third change is to remove the ‘socialise‘ option from the communication pyramid: in conversation with Alexandra Stark I realised that this is covered sufficiently by the ‘utilise’ stage (i.e. making something useful socially).

Replacing that is a new communication option — in fact, two: audiolise and physicalise. This recognises the emergence of sonification as a method of communicating data, and physical methods of representing data from crochet to art installations.

Alongside the updated pyramid I’ve been using for the past few years I also wanted to round up links to a number of resources that relate to each stage. Here they are…

Continue reading →

How to combine two datasets to put a story into context (book extract)

1 Reply
excel for journalists book
This is an extract from Finding Stories in Spreadsheets. Get the book here.

One of the most common challenges in a data-driven story is combining two sets of data — such as events and populations — to put a story into context. In an extract from the ebook Finding Stories in Spreadsheets, I explain how to use lookup functions to combine two tables. The longer ebook version of this tutorial includes a dataset and exercise to employ these techniques.

Combining data is often a great way of telling new stories about spreadsheets. For example: you may have one table showing pass rates for each school in an area, and another table showing their addresses. Combining these would allow you to identify geographical patterns, or to place them on a map.

You could also combine the addresses with poverty rates for different locations, or unemployment to see if there’s a possible relationship (remembering that correlation does not equal causation), or to identify the schools performing particularly well despite local conditions. In the video below, for example, I walk through an example of combining data on different sports teams’ attendances with data on their rankings, allowing you to see who’s attracting large crowds despite their poor performance.

The VLOOKUP function is one of the most widely-used tools in combining data in this way. It stands for Vertical lookup, and means that the spreadsheet will look up and down a column (i.e. vertically) for whatever you ask it. In more recent versions of Excel the XLOOKUP function has been introduced to make the process easier — but the process is similar for both.

Continue reading →

How I hacked my journalism workflow (#jcarn)

3 Replies

I’ve been meaning to write a post for some time breaking down all the habits and hacks I’ve acquired over the years – so this month’s Carnival of Journalism question on ‘Hacking your journalism workflow’ gave me the perfect nudge.

Picking those habits apart is akin to an act of archaeology. What might on the surface look very complicated is simply the accumulation of small acts over several years. Those acts range from the habits themselves to creating simple shortcuts and automated systems, and learning from experience. So that’s how I’ve broken it down:

1. Shortcuts

Shortcuts are such a basic part of my way of working that it’s easy to forget they’re there: bookmarks in the browser bar, for example. Or using the Chrome browser because its address bar also acts as a search bar for previous pages.

I realise I use Twitter lists as a shortcut of sorts – to zoom in on particular groups of people I’m interested in at a particular time, such as experts in a particular area, or a group of people I’m working with. Likewise, I use folders in Google Reader to periodically check on a particular field – such as data journalism – or group – such as UK journalists. Continue reading →