Spreadsheet basics for journalists

GSILogo3No 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, Aug. 19, 2014


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.

NameOld salaryNew salary
Anderson, Daniel4919054109
Brown, Michael5475459682
Davis, Richard4537446735
Garcia, Charles4693849285
Hernandez, Mary5843759606
Jackson, Barbara4493647632
Johnson, John4698649805
Jones, William4741348361
Lee, Susan4054542167
Lopez, Maria5349554030
Martin, Linda5165756823
Martinez, Christopher4465546888
Miller, David5477357512
Moore, Patricia4960151585
Rodriguez, Joseph4160644934
Smith, James4311046990
Taylor, Paul5094754004
Thomas, Mark4450248062
Thompson, Elizabeth4310746556
White, Jennifer5446157184
Williams, Robert5947362447
Wilson, Thomas5570156815

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: Aug. 18, 2017

Ready to try an analysis on your own?

Below are the U.S. Census Bureau’s 2010 and most current (specifically, current as of July 1, 2016) population estimates for each of Tennessee’s 95 counties. I downloaded them from the Census Bureau’s web site, in particular, this page. Using what you’ve learned, calculate each county’s percent change in population between 2010 and now. You’ll see that some counties grew, while other counties shrank. 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.”

County2010 Pop.2016 Pop.Region
Anderson7512675936East
Bedford4510047484Middle
Benton1649316014West
Bledsoe1287414675East
Blount123241128670East
Bradley99126104490East
Campbell4072239714East
Cannon1381414027Middle
Carroll2844528092West
Carter5731356502East
Cheatham3911539880Middle
Chester1718417453West
Claiborne3223431757East
Clay78437752Middle
Cocke3564435219East
Coffee5278354682Middle
Crockett1457414411West
Cumberland5621058655East
Davidson628077684410Middle
Decatur1172911769West
DeKalb1871619361Middle
Dickson4970152170Middle
Dyer3831337708West
Fayette3843539590West
Fentress1792818033Middle
Franklin4097041700Middle
Gibson4973249401West
Giles2940129307Middle
Grainger2271423072East
Greene6882668615East
Grundy1373813389Middle
Hamblen6255063785East
Hamilton337332357738East
Hancock68086577East
Hardeman2715425435West
Hardin2603925679West
Hawkins5686756563East
Haywood1878717853West
Henderson2778627822West
Henry3240432310West
Hickman2464824295Middle
Houston84438134Middle
Humphreys1856918347Middle
Jackson1159611566Middle
Jefferson5169753535East
Johnson1828517754East
Knox433056456132East
Lake78217560West
Lauderdale2774226773West
Lawrence4198843081Middle
Lewis1216211904Middle
Lincoln3341133645Middle
Loudon4873851454East
McMinn5219752850East
McNairy2605625935West
Macon2224523450Middle
Madison9825897663West
Marion2822428446East
Marshall3067831915Middle
Maury8118889981Middle
Meigs1179512005East
Monroe4461845970East
Montgomery173218195734Middle
Moore63406323Middle
Morgan2199921554East
Obion3181530578West
Overton2209622051Middle
Perry79447964Middle
Pickett50725142Middle
Polk1681016772East
Putnam7258075931Middle
Rhea3185932442East
Roane5415952874East
Robertson6639169165Middle
Rutherford263776308251Middle
Scott2224021947East
Sequatchie1413714897Middle
Sevier8997796673East
Shelby928652934603West
Smith1912419447Middle
Stewart1333713182Middle
Sullivan156820156667East
Sumner161249180063Middle
Tipton6107761303West
Trousdale78628271Middle
Unicoi1827717719East
Union1910219140East
Van Buren55575689Middle
Warren3985140516Middle
Washington123423127440East
Wayne1698516713Middle
Weakley3502733507West
White2583826653Middle
Williamson184143219107Middle
Wilson114671132781Middle