Steve Doig Arizona State University
USA
Excel for Journalists
What is “data”? � Information in table form � Columns are the variables
� Name, date, time, address, age, etc.
� Rows are the records � Persons, incidents, etc.
Information, but not data � Steve Doig is a 63-year-old professor who teaches at Arizona
State University.
Now it’s data!
Last name
First name Age Title City
Doig Steve 63
Professor Phoenix Jones Bob 45 Reporter Miami
Smith Tom 34 Reporter New York
Why use Excel? � Good stories can be found in the patterns of data � Human mind alone can’t see the patterns in large sets of
data � Excel has tools to help us see the patterns in data in table
form � Excel can handle large tables
� More than 16.000 columns � More than 1 million rows
A blank spreadsheet
What Excel can do � Import data from many formats � Sort data by one or more variables � Filter data to show only selected rows � Transform data using functions and formulas � Summarize data into categories
Importing data � Common formats
� *.xls (or *.xlsx) � Fixed-width text � Delimited text (comma, tab, etc) � HTML tables
� Data Import Wizard will help
Delimited text example
Fixed-width text
Sorting a table
Now it’s sorted
Filtering: Data…Filter…Autofilter
Pick a category…
…and see just that
Transforming data � Math/stats functions
� Add, subtract, multiply, divide � Average, median, maximum, minimum � Rank, z-scores
� Date/Time functions � Day of week, days between
� Text functions � Extract parts of text strings � Combine strings � Search and replace text
Function Wizard (ƒx)
Function Wizard (ƒx)
Summarizing data � We often want to take a big collection of individual records
and pile them into categories � Trick: Visualize the piece of paper that would give you the
answer you seek � Tool: Pivot tables
Pivot table example � Data: Region, town name, crimes, etc. � Question: “How many crimes occurred in each region?” � Visualize the piece of paper that would answer the question
Building a pivot table
Pivot table
Sorted pivot table
Questions?