Tag Archives: excel

How to: uncover Excel data only revealed by a drop-down menu

Sometimes an organisation will publish a spreadsheet where only a part of the full data is shown when you select from a drop-down menu. In order to get all the data, you’d have to manually select each option, and then copy the results into a new spreadsheet.

It’s not great.

In this post, I’ll explain some tricks for finding out exactly where the full data is hidden, and  how to extract it without getting Repetitive Strain Injury. Here goes…

The example

fire data dropdown

To get the data from this spreadsheet you have to select 51 different options from a dropdown menu

The spreadsheet I’m using here is pretty straightforward: it’s a list of the populations for each fire and rescue authority in the UK (XLS). These figures are essential for putting any story about fires into context (giving us a per capita figure rather than just whole numbers) — and yet the authority behind the spreadsheet has made it very difficult to extract those numbers. Continue reading


How to: analyse your Twitter or Facebook analytics for the best days or times to post

Twitter’s analytics service is a useful tool for journalists to understand which tweets are having the biggest impact. The dashboard at analytics.twitter.com provides a general overview under tabs like ‘tweets’ and ‘audiences’, and you can download raw data for any period then sort it in a spreadsheet to see which tweets performed best against a range of metrics.

However, if you want to perform any deeper analysis, such as finding out which days are best for tweeting or which times perform best — you’ll need to get stuck in. Here’s how to do it. Continue reading

How to: fix spreadsheet dates that are in both US and UK formats


This map by Artem Karimov shows which countries use which data formats

It’s quite common when working with Google Sheets to have data set to US format (Month-Day-Year) without realising it. This is because Google will format your dates based on what ‘locale’ or language you have set – and the default is US English.

Instructions on how to change that are here – but what if it’s too late? What if you’ve already inputted or imported data which, when updated to a different format, will make it the wrong date? Continue reading

Mapping tip: how to convert and filter KML into a list with Open Refine

Original image by GotCredit/Flickr
Original image by GotCredit/Flickr

If you are working with map data that uses the shapes of regions or countries, chances are you’ll need to work with KML. In this guest post (first published on her blog) Carla Pedret explains how you can use the data cleaning tool Open Refine to ‘read’ KML files in order to convert them into other formats (for example to grab the names of places contained in the file).

KML (Keyhole Markup Language) is the default format used by Google’s mapping tool Fusion Tables (Google bought the company which created it in 2004), but it is also used by other mapping tools like CartoDB.

The open source data cleaning tool Open Refine can help you to open, process and convert KML files into other formats in order to, for example, match two datasets (VLOOKUP) or create a new map with the information of the KML file.

What is the difference between XML and KML?

In this post, you will learn how to convert a KML file into XML and download it as aCSV file.

XML – Extensible Markup Language –  is a language designed to describe data and it is used in RSS systems.

XML uses tags like HTML, but there is a big difference between both languages. XML defines the structure of the information, whereas HTML focuses on other elements too, including their meaning and arrangement (even when it is not supposed to focus on appearance), and the importing of other code and media.

KML – Keyhole Markup Language – documents are XML files specific for geographical annotations. KML files contain the parameters to add shapes to maps or three-dimensional Earth browsers like Google Earth.

The big advantage of KML files is the users can customize the maps according to their data and without knowing how to code.

Image of a KML map in Google Fusion Tables
Image of a KML map in Google Fusion Tables

Convert a KML file to XML

You can find KML files using Google Tables search (make sure you have ‘Fusion Tables’ secleted on the left).

Type what you are searching for and add the word geometry or KML.

Captura de pantalla 2016-02-28 a las 19.59.01

Open the fusion table and check that it has shapes by looking for a ‘map’ view (normally this has its own tab).

You should be able to download the KML when looking at that map view by selecting File > Download.

Once downloaded, to convert the file, upload your KML in Open Refine (download Open Refine here) and click Next.KML 1

In the blue box under your data, select XML files.


Now in the preview you can see the XML file with the structure of the information.

If you want to create a map with your own data and the shapes in the KML file, you need to match the KML with your data.

The example I have used contains the shapes of local authorities in the UK. I want to match the shapes in one dataset (the KML file) with information in another dataset on which party runs each council.

The element both datasets have in common (and therefore the element which will be used to combine them) is the name of the councils. But you need to check that those elements are the same: in other words, are the councils named in exactly the same way in both datasets, including the use of ampersands and other characters?

Have a look at the XML preview and try to find the tags that contain the information you need: in this case, authority names. In the example the tags containing the authority name are <name></name>.

Hover over that element so that you get a dotted box like the one shown below. Click on that rectangle and wait until the process has finished.
Captura de pantalla 2016-02-28 a las 20.29.06

You should then see a column or columns as the picture shows.

Captura de pantalla 2016-02-28 a las 20.33.07

On the right hand side of the page, change the name of your file and click on Create a new project.

Once created, you now only need to export it. Click on Export and select the format you prefer.


What originally was a KML file is now a filtered list with data ready to check and match against your other dataset.

Do you use Open Refine? Leave a comment with your tips and techniques or send it to me at @Carlapedret..

Continue reading

How to: calculate or find rankings in spreadsheets using RANK, LARGE and SMALL

The ebook version of this tutorial includes a dataset and exercise to employ these techniques.

Right at the start of my book on Excel for journalists I talk about sorting data to find out which values come top or bottom. However, there is a family of functions which will give you a lot more control in finding out not just who is top or bottom, but the rank of any value in any series of values.

This is particularly useful if you want to compare ranks.

Pakistan ranking story

Many stories are based on finding out where your own country or region ranks in the latest data

Consumer ranking story

Ranking isn’t just about statistics – it can be used in consumer stories too

For example, say you had a table showing school performance across the last two years.

Each table shows the percentage of pupils achieving the top grades in that year. You can use RANK to find out what rank each percentage would have placed the school in for each year. Continue reading

HOW TO: Find out the ages of people using Excel

excel for journalists ebook

This post is taken from the ebook Finding Stories With Spreadsheets

“How do I calculate an age in Excel?” Marion Urban, a French journalist and student on the MA in Online Journalism in Birmingham, was preparing data for the forthcoming UK General Election.

In order to do this Marion had downloaded details on the candidates who had stood successfully in the previous election.

“It was a very young intake. But it wasn’t easy to calculate their ages.”

Indeed. You would think that calculating ages in Excel would be easy. But there is no off-the-shelf function to help you do so. Or at least, no easy-to-find function.

Instead there are a range of different approaches: some of them particularly, and unnecessarily complicated.

In this extract from Finding Stories in Spreadsheets I will outline one approach to calculating ages, which also illustrates a useful technique in using spreadsheets in stories: the ability to break down a problem into different parts. Continue reading

Finding Stories in Spreadsheets – ebook now live!

Finding stories in spreadsheets book cover

Cover design by Matt Buck/Drawnalism

My latest ebook – Finding Stories in Spreadsheets – is now live on Leanpub.

As with Scraping for Journalists, I’m publishing the book week-by-week so the book can be updated based on reader feedback, user suggestions and topical developments.

Each week you can download a new chapter covering a different technique for finding stories, from calculating proportions and changes, to combining data, cleaning it up, testing it, and extracting specific details.

There’s also a downloadable spreadsheet at the end of each chapter with a series of exercises to practise that chapter’s technique and find particular stories.

Along the way I tackle some other considerations in telling the story, such as context and background, and the importance of being specific in the language that you use.

If there’s anything you’d like covered in the book let me know. You can also buy the book in a ‘bundle’ with its sister title Data Journalism Heist, which covers quick-turnaround techniques for finding stories in spreadsheets using pivot tables and advanced filters.