Using Excel To Do Data Journalism
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 (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. The population for each fire authority is on the second sheet of the workbook. The sheet defaults to figures for the population of England from 1971 to 2016 — to get the data for every authority in this spreadsheet you have to select another 50 different options from a dropdown menu, one by one. There must be a better option. Where is the data? Select a cell with data and the formula bar will show a complex formula The first step is to click on one of the cells containing the numbers you want — the numbers that change when a different option is selected from the drop-down menu.
When selected, look in the formula bar above the sheet (but below any menus) — highlighted with the red box in the image above. The box will probably contain a formula that provides a clue to the location of the missing data. This one is quite a mouthful — but you don’t have to understand it to spot the name of a sheet which is not visible: =IF(HLOOKUP($B$4,'rawdata and checks'!$B:$BB,A6-1969,FALSE)='.' ,ROUND(HLOOKUP($B$4,'rawdata and checks'!$B:$BB,A6-1969,FALSE),-2)) The formula is looking in a sheet called 'rawdata and checks' (the exclamation point is a useful clue to look for).
That must be where the data is located. But this spreadsheet doesn’t have a sheet with that name — or at least one that we can see. It must be hidden Find a hidden sheet in Excel Instructions on how to hide a sheet in Excel — and, more importantly, how to unhide them —. In some instances that will be the end of the process. However, in this particular example the instructions don’t work — possibly because, as a note on that page explains: “Note: If worksheets are hidden by Visual Basic for Applications (VBA) code that assigns the property xlSheetVeryHidden, you cannot use the Unhide command to display those hidden sheets.
If you are using a workbook that contains VBA macros and you encounter problems when working with hidden worksheets, contact the owner of the workbook for more information.” Alternatively, the hidden information. So we need a different approach Experiment with cell references to the hidden sheet Let’s go back to the formula which is fetching the data from that hidden sheet.
If that formula can fetch the data, then perhaps we can create more formulae which will cumulatively fetch all of it. We could write a formula that grabs any specified cell from that sheet, like so (this is best done below the table so you have plenty of room): ='rawdata and checks'!A1 That cell is empty – but try copying it down one cell, so that it reads: ='rawdata and checks'!A2 This time the formula fetches a number: 1971. That’s the first year in the table above — a positive sign.
Using Excel To Do Data Journalism Work
Now try copying the formula one to the right, so that it reads: ='rawdata and checks'!B1 The formula fetches ‘England’. At this point you can start to copy these cells across and down until you have covered all the years, and all the countries — and the full dataset is there to be reused. Another mystery: finding the hidden values for the drop-down list.
Use the Data Validation option to see the values behind a drop-down list In this example the values from the drop-down list are on the other sheet too — but what if they’re not? To find the list that the drop-down menu is using you can look at, and reverse-engineer those. This is how to do it:. Click on the cell which is being used for the drop-down list. Select the.Data. menu, and then click on.Validation.
(or the.Data Validation. button). A.Data Validation. window should appear (shown above). On the default.Settings.
view you should be able to see the Validation criteria. The third box on that view is.Source:. This tells you which cells are being used to create a the drop-down list. In the Fire Service example above, that is `=$G$6:$G$56`. Make a note of the cell range and exit the window by clicking.Cancel. Look in the spreadsheet for those cells.
It’s likely that they will have been hidden: if you can see the cells but not the values, try changing the text colour of those cells to black (they may have been made white to make them invisible). If you can’t see the cells, the column may have been hidden (see image below): in that case hover over the border between the two column letters until you get a two-lined double-arrow-headed cursor which allows you to click and drag to expand the hidden column.
Ebook Scraping for Journalists available on Leanpub Follow Blog via Email Enter your email address to follow this blog and receive notifications of new posts by email. Join 2,472 other followers. Few people are actually trapped in filter bubbles. Why do they like to say that they are? » Nieman Journalism Lab. RT @: That’s my Santa work done for the year!! Happy to Christmas to all you wonderful @ lot 🎄🎅🏼🎄.
RT @: The question of impact in data journalism is examined in @'s chapter:. RT @: Sure Start funding cut by 50pc in Birmingham since 2010 - by me and the splendid @.
RT @: Know someone who would benefit from one of 4 paid Cosmo scholarships? We're looking for ANYONE (no qualifications needed Tweets about OJB posts Top Posts.
You’re a budding data journalist and you’ve got your head around the basics – you’ve played around with Microsoft Excel, you’ve made a few charts here and there, and the thought of statistics doesn’t give you nightmares anymore. But looking for some guidance on what to do next?
Well, you’re in the right place. With useful advice from some of the Interhacktives alumni, here’s our guide on how to get started in data journalism. Let your curiosity guide you There’s data available on just about everything. “Apply data to your personal interests,” says ex-Interhacktive Peter Yeung. “Context is of the utmost importance. Why is there a pattern?
How does this compare to historical trends? Do experts agree? Is the data robust? Does it provide the whole picture or is it limited in some way? Who is providing it – a group with an interest?” Finding the data isn’t as difficult as you think. More data than you can imagine is available online. You can look for UK national data on the, global health data on the, and other public data on the websites of different public departments (like the ).
If the data isn’t explicitly available in a data set, you can scrape it from a website! Jitsi for mac. Find out how to scrape data using R.
Face your fear of numbers “Don’t be scared of numbers,” former Interhacktive Debora Aru advises. You might have gone into journalism hoping to never see numbers again. Not all hope is lost. “It’s true that data journalism is based on numbers, but it doesn’t mean you need to be a nerd to work with them.” Peter says: “Don’t get bogged down in the numbers, focus on why the work is important and who it will affect.” While dealing with statistics might sound intimidating, take comfort in knowing that the computer will do most of the work for you! Once your data is cleaned, you just need to know where to put your numbers. Explore different tools and programmes So many different tools and programmes are available (many of them free) to make data journalism just that much more fun.
With the amount of options out there, you’re bound to find at least one you’re comfortable using. Debora says: “I suggest starting with learning a programming language like Python or R Studio as soon as possible because it’s really helpful when you need to analyse huge dataset quick.” When it comes to data analysis and/or, other available options include Excel, SPSS, Tablaeu, Carto, Datawrapper, Flourish, and many more. Click for tutorials on. If these seem too out of your comfort zone, Niamh McIntyre, another Interhacktives alumna, has some reassuring words. “You don’t have to be (and never will be) a data scientist – learning to code is a great add-on skill, but in all honesty a high proficiency in Excel will get you a long way.” You may also want to to ease your transition into data journalism.
These include apps for note-taking, social media, and staying up-to-date on the news in the field. Networking and events Now that you’re familiar with the tools of the trade, get to know the people in the field. The best ways to do this is to follow them on Twitter and attend events like Hacks/Hackers. We have compiled a to attend in the next few months – make sure to check them out! Final words by those who made it “Data journalism is a great niche to get into, because there are hundreds of journalists who can write great copy, but hardly any who can work with data effectively. As a data journalist, a large part of your job will involve Freedom of Information stories, so get to know your way around the FOI Act well, and how to deal with being fobbed off by FOI officers – one mistake in a request could mean adding another month to your the timescale of your project.” – Niamh McIntyre : Interhacktives 2016-2017 – now a data journalist at Press Association. “Always review your working out. Never manipulate the numbers to fit your narrative.
Beware of confirmation bias. Have fun and don’t be too serious about it. Make your work as lucid and easily understandable to as many people as possible.” – Peter Yeung Interhacktives 2015-2016 – now an interactive journalist at The Times. “Never forget to have a nose for stories. Data can tell amazing stories but if you can’t spot them, it can be quite useless and boring. So, when you’re approaching a dataset always ask yourself: “If I see this story on a newspaper, would I ever be interested in it?” If the answer is “Uhm not really”, probably not even readers will be interested in your story.” – Debora Aru : Interhacktives 2015-2016 – now a data journalist at Trinity Mirror Data Unit.
Got any more tips for data journalism newbies? Let us know in the comments below!