No matter what you end up doing in media, you’ll almost certainly end up needing to make sense of some data. And you’ll probably need to do it both perfectly and in a hurry, because that’s how media people are expected to do everything. This three-part minicourse can help you be ready. It will teach you some spreadsheet basics using Google’s free, online spreadsheet app, Google Sheets. You’ll also pick up a few insider skills that could truly set you apart. Give it a try. I think you’ll be glad you did.
— Ken Blake, May 11, 2019
Practice data: Proposed raises
Throughout the course, you’ll be working with this made-up dataset. Imagine it describes the original and new salary for each of 22 city department heads who would receive pay raises under a measure being proposed by the local mayor.
Name | Old salary | New salary |
---|---|---|
Anderson, Daniel | 49190 | 54109 |
Brown, Michael | 54754 | 59682 |
Davis, Richard | 45374 | 46735 |
Garcia, Charles | 46938 | 49285 |
Hernandez, Mary | 58437 | 59606 |
Jackson, Barbara | 44936 | 47632 |
Johnson, John | 46986 | 49805 |
Jones, William | 47413 | 48361 |
Lee, Susan | 40545 | 42167 |
Lopez, Maria | 53495 | 54030 |
Martin, Linda | 51657 | 56823 |
Martinez, Christopher | 44655 | 46888 |
Miller, David | 54773 | 57512 |
Moore, Patricia | 49601 | 51585 |
Rodriguez, Joseph | 41606 | 44934 |
Smith, James | 43110 | 46990 |
Taylor, Paul | 50947 | 54004 |
Thomas, Mark | 44502 | 48062 |
Thompson, Elizabeth | 43107 | 46556 |
White, Jennifer | 54461 | 57184 |
Williams, Robert | 59473 | 62447 |
Wilson, Thomas | 55701 | 56815 |
You’ll learn how to use Google Sheets to capture these figures, analyze them, and come up with the information and data visualization needed to write a post like this one:
Mayor proposes nearly $60,000 in staff raises (Click to see the post)
Google Sheets is free. All that’s required is an Internet-connected PC or Mac. Google Sheets works the same way on either type of computer. An experienced user could produce everything needed for the post, including the graphic, in about five minutes. Here’s a video demonstration, in real time, of the techniques you’ll learn.
A three-part course in learning to use Google Sheets
Part 1: Making a plan & getting started. It usually pays to spend a few minutes thinking about what you might want to learn from a dataset before you start analyzing it. This tutorial looks at what might be newsworthy about the raises dataset, shows you how to create a Google Sheet, and introduces you to fundamentals like rows, columns and cells. Finally, it shows you how to produce and replicate a simple computation.
Part 2: Describing and comparing the raises. Part 1 covered the basics of setting up and using a spreadsheet. This lesson gets down to the business of discovering who got the biggest and smallest raises, what the average raise was, the total amount of money the raises will cost the city, and other things you’d need to know to write a thorough, accurate story about the raises.
Part 3: Making an interactive graphic. You might be surprised by how easy it is to add a basic, online, interactive data visualization to your reporting. This lesson will show you how to do it using Google Sheets’ built-in, shareable chart templates.
An exercise: Tennessee county population estimates
Last updated: May 19, 2023
Ready to try an analysis on your own?
Below are the U.S. Census Bureau’s 2010 and latest-available annual population estimates for the 20 Tennessee counties that have 65,000 or more residents. In all, Tennessee has 95 counties. Counties with fewer than 65,000 residents are not shown. I excluded the smaller counties for two reasons. First, doing so keeps the exercise simpler. Second, the Census Bureau doesn’t produce annual single-year estimates for counties with populations smaller than 65,000. For smaller counties, the Bureau produces five-year range estimates that would not be directly comparable with the annual estimates shown.
Using what you’ve learned, calculate each county’s percent change in population between 2010 and now. You’ll see that most – but not all – of the counties have grown in population since 2010. Which counties had the highest growth rates – that is, had the greatest percent increases in their populations? Which ones had the lowest? Next, produce and share an interactive data visualization showing the 2010 and current populations for each of the 12 counties with the largest current populations. The chart should look something like the Part 3 chart titled, “Meet the 12 highest-paid department heads under the mayor’s proposed city budget.” Finally, write a news story about the county population changes, using the results of your analysis, your chart, and information and quotes from this (made-up) background information. If you’re doing this exercise for a class, follow the specific directions your professor gives you.
Don’t worry; while these data are about population estimates rather than salaries, the dataset is structured essentially the same way as the salary data were structured. So you can do to these data what you did to the salary data above. There are some differences between the population data and the salary data that you should be aware of, though. See “Tips for analyzing the population data.”
County | 2010 Population | 2021 Population | Region |
---|---|---|---|
Anderson | 75160 | 77576 | East |
Blount | 123119 | 137605 | East |
Bradley | 99078 | 110162 | East |
Davidson | 628133 | 703953 | Middle |
Greene | 68797 | 70621 | East |
Hamilton | 337294 | 369135 | East |
Knox | 433108 | 486677 | East |
Madison | 98395 | 98775 | West |
Maury | 81141 | 104760 | Middle |
Montgomery | 173307 | 227900 | Middle |
Putnam | 72564 | 81188 | Middle |
Robertson | 66469 | 74098 | Middle |
Rutherford | 263740 | 352182 | Middle |
Sevier | 90072 | 99517 | East |
Shelby | 929030 | 924454 | West |
Sullivan | 156794 | 159265 | East |
Sumner | 161250 | 200557 | Middle |
Washington | 123338 | 134236 | East |
Williamson | 184035 | 255735 | Middle |
Wilson | 114537 | 151917 | Middle |