Scatterplots:
Bureau of Labor Statistics: Job Openings and Labor Turnover Survey (2013-2023)
Using data from the U.S. Bureau of Labor Statistics, we will build scatterplots in Microsoft Excel that compare and relationships between time and different labor statistics:
We will cover how to create and edit scatterplots in Excel. |
We will use four data tables from the Bureau of Labor Statistics. The data tables are in Excel format and can be downloaded to your computer from the link above.
Download data from link above.
Tabs | Variables |
---|---|
Job Openings | Year, Month, Number of job openings |
Layoffs and Discharges | Year, Month, Number of people laid-off and/or discharged from their job |
Hires | Year, Month, Number of people hired |
Quits | Year, Month, Number of people who quit their jobs |
Instructions for Creating a Scatterplot
1995-2018 Study: High Casualty Terrorist Bombings, State Fragility Index, and Democracy Score
Using data from the Center for Systemic Peace, we will build visualizations in Microsoft Excel that compare proportions and relationships between three factors:
We will cover the following Excel functionality:
|
We will use three data tables from the Center for Systemic Peace. As the tables provided by the Center for Systemic Peace are served using an insecure connection, the three tables have been pre-downloaded and checked for viruses.
Table | Variable | Metadata Description |
---|---|---|
High Casualty Terrorist Bombings, 1989-2020 | death |
|
Polity5 Annual Time-Series, 1946-2018 | sfi |
|
State Fragility Index and Matrix, Time-Series Data, 1995-2018 | polity |
|
Purposes of this step: (1) Clear unneeded columns from SFI table to avoid confusion with too many columns. (2) Add the POLITY variable to the SFI table.
Procedure | Visual View |
---|---|
Clean SFI Table
|
|
Create a Unique Identifier for SFI Table
=B2&D2
|
|
Create a Unique Identifier for Polity5 Table Repeat steps above for this table, but the formula should be: =E2&G2 |
|
Use VLOOKUP to Add the POLITY Column to the SFI Table
=VLOOKUP(A2,'Polity5 Annual Time-Series'!A:L,12,FALSE)
|
Purposes of this step: The High Casualty Terrorist Bombing table organizes records (rows) by event, not by year. We need to restructure the table so that we can sum the casualties per year per country.
Procedure | Visual View |
---|---|
Create a Unique Identifier for Casualty Table
=B2&G2 |
|
Insert a Pivot Table
|
|
Setup Pivot Table
|
Purpose of this step: Add the SUM OF DEATH variable to the SFI table.
Procedure | Visual View |
---|---|
Use VLOOKUP to Add the SUM OF DEATH Column to the SFI Table
=VLOOKUP(A2,Sheet1!A:B,2,FALSE)
|
|
Convert the #N/A Values to 0
=IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),0) |
Procedure | Visual View |
---|---|
Add a Filter Dropdown to Each Row
|
|
Filter the YEAR Column to 2001
|
|
Create Map
|
Procedure | Visual View |
---|---|
Create Scatterplot
|
|
Improve Scatterplot Layout
Change the Year! |
|
Difficulties working through this exercise? | Download Here |
Copyright © Baylor® University. All rights reserved.
Report It | Title IX | Mental Health Resources | Anonymous Reporting | Legal Disclosures