 |
Ken Blake, Ph.D. Associate Professor Middle Tennessee State University School of Journalism |
Learning to use a spreadsheet will transform how you do what you do as a media professional. The YouTube-hosted videos below demonstrate some of the possibilities. If you like, download the datasets and follow along.
I use the PC version of Excel 2010, Microsoft's cheap, ubiquitous spreadsheet program. It can do some things that the Mac version can't. But if you're a Mac user, you'll still find plenty worth watching.
Everything is free to watch and download. If you'd like to use these materials elsewhere, please just e-mail me first and ask permission. Questions, suggestions and requests are welcome, too. See my contact information.
And check back now and then. I'll be adding more stuff soon.
Introducing Excel
- Brand new to Excel? Start here. "Introducing
Excel" covers what you need to know to get going, including
rows, columns, cell addresses, formula writing, copying and pasting,
sorting, and basic functions. Download this dataset to follow along
with what you see in the video.
- Manually keying data into Excel is boring. You're also quite likely to make all kinds of mistakes. Knowing how to import data into Excel will save you loads of time and frustration. "Importing data into Excel" shows you some basic techniques that will get you through most situations. The datasets in this compressed file will let you follow along.
Maximizing Excel
- Ready for some more advanced Excel stuff? "Rates,
ranks and filters in Excel" introduces you to some powerful
tools for comparing things - in this case, murder rates for
different U.S. cities. Here's the data file
you'll need to try it for yourself.
- "Computing
ratios in Excel" shows you how to evaluate how balanced - or
unbalanced - two things are, like arrests of drug dealers and drug
users. Want practice? Here's the data file,
which I've snagged from an exercise developed by Rich Gordon of the
Miami Herald.
- For a specialized, but truly handy, tool, watch "Subtotaling
in Excel." You'll see how to make short work of aggregating
political campaign donor information to learn who a politician's
true best friends are. As always, here's your
data file.
- And now for something that will truly set you apart. "Excel
and pivot tables" will show you how to quickly count types of
things and look for patterns that suggest they are connected. For
example, might a certain traffic court judge be giving preferential
treatment to male traffic offenders? Here are the
data.
- Sometimes, the best way to spot a pattern in data is to represent the data visually. "Producing graphics in Excel" will show you how to create and edit column charts, pie charts and scatterplots. Follow along with this dataset.
Extending Excel
- Excel can do even more if you pair it with other applications. "Excel
and Microsoft Access" describes how to bring two Excel files
into the Microsoft Access relational databse program and crosscheck
the files for matching records. Such checks have underpinned a lot
of investigative journalism projects. Here is a
.zip file
containing the practice datasets.
- Sometimes, newsworthy patterns have more to do with where things
are than with what they add up to. Pairing Excel with Google Fusion
Tables can help you produce Web-based, interactive maps. For
example:
-
Excel and mapping points with Google Fusion Tables shows you
how to make a map showing whether any registered sex offenders
live too close to schools or daycare centers. You might need to
add the Google Fusion Tables app to your Google Drive account,
as shown in
this video. Also,
here's the data file.
- Excel
and mapping polygons with Google Fusion Tables describes how
you can map things that have borders, like counties, then shade
them according to some criteria, like their unemployment rates.
You'll need
these practice files.
-
Excel and mapping points in polygons with Google Fusion Tables
shows you how to combine the above two skills to make maps that
not only show the borders of things but also show where specific
things are within those borders. The example creates a map
showing state legislative districts and where each district's
candidates live. Here are the
practice
files.
-
Excel and mapping points with Google Fusion Tables shows you
how to make a map showing whether any registered sex offenders
live too close to schools or daycare centers. You might need to
add the Google Fusion Tables app to your Google Drive account,
as shown in
this video. Also,
here's the data file.
Coming soon:
- Excel and basic inferential statistics using the Data Analysis
ToolPak
- Excel and content analysis using the free Yoshikoder application