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

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, 2015) population estimates for each of Tennessee’s 95 counties. I downloaded them from the Census Bureau’s web site. 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.2015 Pop.Region
Anderson7512975749East
Bedford4505847183Middle
Benton1648916129West
Bledsoe1287614502East
Blount123010127253East
Bradley98963104091East
Campbell4071639752East
Cannon1380113840Middle
Carroll2852227910West
Carter5742456486East
Cheatham3910539741Middle
Chester1713117471West
Claiborne3221331709East
Clay78617771Middle
Cocke3566235162East
Coffee5279654277Middle
Crockett1458614601West
Cumberland5605358229East
Davidson626681678889Middle
Decatur1175711660West
DeKalb1872319182Middle
Dickson4966651487Middle
Dyer3833537893West
Fayette3841339165West
Fentress1795917917Middle
Franklin4105241449Middle
Gibson4968349399West
Giles2948528946Middle
Grainger2265722846East
Greene6883168580East
Grundy1370313441Middle
Hamblen6254463402East
Hamilton336463354098East
Hancock68196572East
Hardeman2725325707West
Hardin2602625756West
Hawkins5683356471East
Haywood1878718023West
Henderson2776928015West
Henry3233032147West
Hickman2469024363Middle
Houston84268149Middle
Humphreys1853818135Middle
Jackson1163811509Middle
Jefferson5140753240East
Johnson1824417830East
Knox432226451324East
Lake78327576West
Lauderdale2781526936West
Lawrence4186942564Middle
Lewis1216111854Middle
Lincoln3336133743Middle
Loudon4855651130East
McMinn5226652639East
McNairy2607526066West
Macon2224823177Middle
Madison9829497610West
Marion2823728487East
Marshall3061731552Middle
Maury8095687757Middle
Meigs1175311830East
Monroe4451945771East
Montgomery172331193479Middle
Moore63626322Middle
Morgan2198721498East
Obion3180730639West
Overton2208322129Middle
Perry79157929Middle
Pickett50775146Middle
Polk1682516773East
Putnam7232174553Middle
Rhea3180932526East
Roane5418152753East
Robertson6628368570Middle
Rutherford262604298612Middle
Scott2222821950East
Sequatchie1411214811Middle
Sevier8988995946East
Shelby927644938069West
Smith1916619295Middle
Stewart1332413259Middle
Sullivan156823156791East
Sumner160645175989Middle
Tipton6108161870West
Trousdale78708042Middle
Unicoi1831317860East
Union1910919119East
Van Buren55485677Middle
Warren3983940435Middle
Washington122979126302East
Wayne1702116748Middle
Weakley3502133960West
White2584126521Middle
Williamson183182211672Middle
Wilson113993128911Middle