The prices of my 3 data journalism ebooks — Data Journalism Heist, Finding Stories in Spreadsheets and Scraping for Journalists — have been cut to $5 on Leanpub in the lead up to Christmas. And if you want to get all 3, you can also get the data journalism books bundle on Leanpub for more than half price over the same period, at $13. Get them while it lasts!
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
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.
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
A Spanish translation of my ebook ‘Finding Stories in Spreadsheets’ is now live. The book is available for an initial special discount price of $4.99 for ‘early adopters’.
Meanwhile, I’m continuing to add chapters to the English version, which you can download from here. The latest chapters deal with using query-based URLs in Google Sheets, and generating random numbers for mapping.
A Spanish version of Finding Stories in Spreadsheets – titled ‘Excel para periodistas’ (Excel for Journalists) – should be available in the next few weeks.
The translation is being done by Barbara Maseda. If you want to know when it’s going to be available you can register to be informed on the book page.
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
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.