Political Science 138:
Quantitative Reasoning in Political Science
 Home Up

 

HW3: Create a table in Excel, using Cross-National Data.
 [These instructions updated for Excel 2007]

Specific skills involved:

  • Finding OECD data
  • Selecting a relevant set of data from the downloaded dataset
  • Constructing a table:
    • using formulas to construct a measure
    • sorting data
    • merging cells
    • cell alignment and word wrap, centering data in columns, adjusting column widths
    • setting decimal places
    • using borders

Due: September 5, Friday, 8:01am.

Note: Before doing this lab assignment, you should have read the JPDA, Chapter 2.

Goal: To create a table in Excel that looks like table 1, with these features:

  • Country-level data for two years.
  • Data sorted on the base year.
  • Formulas used to calculate the change in the third column.
  • Merged cells and text-wrap for the title.
  • Horizontal borders under the title and headings.
  • Source listed at the bottom of the table.

In this exercise, you should learn how to use the following Excel functions:

  • sorting data.
  • formulas (to calculate the average and net change).
  • copying and pasting formulas (so you don't have to retype them).
  • merging cells and wrapping text (the title and footnote for this chart is in four merged cells.
  • drawing lines in cell borders.
  • aligning text and setting decimals.

Instructions:

Find the Data: 

  • One of the best source of cross-national data for developed nations is the OECD.   Go to the SourceOECD website for an alphabetical index of data by topic.

  • Also, Section 30 of the Statistical Abstract (that we used on the Data Search Assignment).
    (note this links to the Excel files for each table)

  • And the International section of the JPDA "Finding the Data" website

  • For some topics, you can try a google search, e.g.:
    international tax rates
    international murder rates
    international gun ownership rates,
     "by country" gun ownership rates

  •  other good data

    (but note that you may have difficulty getting the data into Excel)

Open the Excel file for that table (or paste the data into an Excel file). Note that in some of the Excel files there will be several worksheets of data, scan through these to select the best data.

  • Begin by deleting all the unnecessary rows and columns.
  • Use formulas* to calculate net change or percentage change in the third column.
  • Adjust the column widths (use the mouse to drag the widths).
  • Be sure to use merge and wrap in the cell formatting:
    Highlight the cells to be merged and:
    use Format | Cells | Alignment -- check word wrap and merge to merge cells and wrap lines.
  • use Format | Cells | Border to draw lines.
  • To sort the data, highlight the entire set of rows and columns to be sorted, then select:
    Data | Sort
  • Change the fonts from Courier to something that looks nicer.

Save the Excel file -- AS AN EXCEL WORKBOOK FILE -- on your website drive.

Save a backup copy on your H:\ drive.

Open your Welcome.htm file in Word and create a link to the excel file.

Notes:

1. Formulas:

On the right, notice the formula in the formula bar for cell D7.

To create a formula in cell E4, enter:
        =C7-B7
Then copy that cell down the whole column (this can be done by dragging the little square on the bottom right hand side down the column).  Excel will automatically recalculate the formula for the entire column.

In some cases, you may need to calculate a percentage change rather than a net change.  For example:
        =(C7-B7)/B7*100

2. Merging Cells

The Cells A5 thru D5 have all been merged into a single cell using the merge and center button.

3. Aligning Columns

Unfortunately, centering the column of data does not properly align the decimal points of the column if the numbers have different numbers of digits.   Instead, try right justifying the numbers and using the indent functions to center the column.

4. Decimals.

It may also be necessary to adjust the number of decimal places, particularly for the newly calculated "change" variable.  Highlight the cells, right click and choose "format cells"

Format Cells | Number (and specify the number of decimal places)

5. General formatting.

Various commands on the Font, Alignment and Number
portions of the "Home" ribbon will format the appropriate cells.

6.  Sort the data:

Highlight **ALL** the cells to be sorted and choose SORT from the Data Ribbon.

This is best done by highlighting the entire rows of data to be sorted.

 

Other good data: Cross-National Data Sources.

Below are some links to data on each of the profile topics.  Note that several of these sites have other data on your topics.  Several of these sites will have more data (more countires and more variables) than you need for this exercise. It is your responsiblity to select meaningful data.

see also: project resources page.

(Note on downloading data: use Internet Explorer to copy data from a web page, be sure to del4te non-numeric data from cells)

International Data:

Sources of International data:  (See also the "Finding the Data" chapter in JPDA)

Abortion

·       Gutmacher report (choose two years) (see downloading data note above)

Voter Turnout:

·       Fair Vote (this has a good selection of countries, see downloading data note above)

·       International Institute for Democracy and Electoral Assistance  (turnout data)

Education Achievement 

·       TIMMS data

·       NCES, Education Indicators: An International Perspective (tables) (fewer countries, more data)

·       UNESCO

Women’s Representation

·       Global Database of Quotas for Women

·       Women in national parliaments

Crime and Punishment

·       BJS International statistics

·       Murder Rates (Handgun Murder rates)

·       Incarceration rates

·       Victimization survey (use recent year, replace commas with decimals points!)

Infant Mortality

·       March of Dimes website  (IM rates) (other statistics)

·       CDC data

Other International Data