Skip to Main Content

PSC 4396 - International Studies Senior Seminar

General Links

What is a Scatterplot?


  • are graphs containing 2 numeric variables plotted against each other to allow you to see the relationship between these variables. 
  • are often used in conjunction with a correlational analysis, which statistically tells you the strength of the relationship between the 2 variables.
  • visually display the relationship between 2 variables; they can show you what kind of relationship the variables have. For example, if one variables goes up, and so does the other - the variables have a positive relationship (+/+). Or if one variable goes up but the other goes down - the variables have a negative relationship (+/-).

Exercise #1 - Scatterplots in Excel

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:

  1. Job Openings
  2. Layoffs and Discharges
  3. Hires
  4. Quits


We will cover how to create and edit scatterplots in Excel.

Exercise #1 Steps

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

  1. Decide which data you want to create the scatterplot of. You can select 2 columns or rows of data.
  2. Click Insert on the top toolbar. 
  3. Then click on the Scatterplot icon in the middle, and click the top left option. This will create a blank scatterplot.
  4. Click inside the blank scatterplot. Click on Select Data at the top.
  5. Now select the 2 columns (or rows) you want to visualize in the scatterplot.
  6. Edit the Legend Entries' name to be the variable name.
  7. Click Ok.
  8. Now you can edit the appearance of the scatterplot - add axis titles, a chart title, change the scaling, change the colors, etc.

Exercise #2 - Concatenate and VLOOKUP in Excel

1995-2018 Study: High Casualty Terrorist Bombings, State Fragility Index, and Democracy Score

Logo for Center for Syetmic Peace

  Using data from the Center for Systemic Peace, we will build visualizations in Microsoft Excel that compare proportions and relationships between three factors:

  1. Casualty counts from terrorist bombings
  2. State Fragility Index (SFI)
  3. Democracy Score


We will cover the following Excel functionality:

Exercise #2 Steps


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.

Download data here.

Table Variable Metadata Description
High Casualty Terrorist Bombings, 1989-2020 death
  • Casualties per event

Polity5 Annual Time-Series, 1946-2018 sfi
  • State fragility index
  • Higher score represents higher fragility
State Fragility Index and Matrix, Time-Series Data, 1995-2018 polity
  • Provides a single regime score that ranges from +10 (full democracy) to -10 (full autocracy)
  • Score of -66: Interruption Periods: Period of "interruption". Operationally, if a country is occupied by foreign powers during war, terminating the old polity, then reestablishes a polity after foreign occupation ends, Polity codes the intervening years as an interruption until an independent polity is reestablished.
  • Score of -77: Interregnum Periods: indicates periods of “interregnum,” during which there is a complete collapse of central political authority.

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

  • Open the State Fragility Index table
  • Delete all columns after sfi

Create a Unique Identifier for SFI Table

  • Add a new column at the start of the table by right-clicking A and selecting Insert.
  • Name the new column UNIQUE
  • In the first cell under the UNIQUE column, enter the following formula:


  • On the bottom-right corner of the cell, which should now read AFG1995, double-click. This should copy the formula for all rows going down.

Create a Unique Identifier for Polity5 Table

Repeat steps above for this table, but the formula should be:


Use VLOOKUP to Add the POLITY Column to the SFI Table

  • In SFI table, on column G, name it polity.
  • Enter the following formula into the first cell in this column:

=VLOOKUP(A2,'Polity5 Annual Time-Series'!A:L,12,FALSE)

  • Then copy this formula for this entire column.

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



Insert a Pivot Table

  • In the High Casualty Terrorist Bombing table, click Insert and then click PivotTable.
  • In the popup, leave all defaults and Ok.

Setup Pivot Table

  • Drag UNIQUE to the Rows box
  • Drag DEATH to the Values box.


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

  • In SFI table, on column H, name it casualties.
  • Enter the following formula into the first cell in this column:


  • Then copy this formula for this entire column.

Convert the #N/A Values to 0

  • Modify the expression to the following:



Procedure Visual View

Add a Filter Dropdown to Each Row

  • On the top ribbon, click Sort & Filter and then click Filter.
  • This should result in a dropdown menu at the top of each column.

Filter the YEAR Column to 2001

  • Click the YEAR dropdown
  • Uncheck Select All
  • Check 2001

Create Map

  • Select the Country field
  • Hold Control (Command on Mac) and select the CASUALTIES column.
  • On the top ribbon, click Insert Maps ad then Filled Map.

  • Experiment with other years!


Procedure Visual View

Create Scatterplot

  • Filter the -88, -77, and -66 Values Out of POLITY Column
  • Select the SFI and POLITY columns and click Insert and then Scatterplot

Improve Scatterplot Layout

  • Select the chart and click Chart Design at the top bar
  • Under Quick Layout, click Layout 9

Change the Year!

Difficulties working through this exercise? Download Here


University Libraries

One Bear Place #97148
Waco, TX 76798-7148

(254) 710-6702