Data Collection Exercise

This exercise is designed to make you comfortable with downloading and using Educational spreadsheets from the National Center for Educational Statistics.

We are going to answer the question: How have the white & minority student demographics in the public high schools in your town changed over the last 5 years?

1. Pick a partner and choose one of your home towns.

2. Go to

2. Select Table Generator, and click “I agree” to the terms and conditions.

3. Under “Select a Table Row,” select for a Public School.

4. Under “Select Years,” select 2012-13 AND 2007-08.

5. Under “Select Table Row,” select the following:

Information>>Contact Information select

  • Location Address [Public School]
  • Location City [Public School]

6. Under Characteristics –> Grade Span Information – select “School Level Code” to determine if the school is a high school.

7. Under Enrollments select

Total Enrollment

Check or uncheck all yearsTotal Students [Public School]
2012-13 2007-08


Enrollment by Race Ethnicity

  • Check or uncheck all yearsAmerican Indian/Alaska Native Students [Public School]
    2012-13 2007-08
  • Check or uncheck all yearsAsian or Asian/Pacific Islander Students [Public School]
    2012-13 2007-08
  • Check or uncheck all yearsHispanic Students [Public School]
    2012-13 2007-08
  • Check or uncheck all yearsBlack Students [Public School]
    2012-13 2007-08
  • Check or uncheck all yearsWhite Students [Public School]
    2012-13 2007-08
  • Check or uncheck all yearsHawaiian Nat./Pacific Isl. Students [Public School]
  • Check or uncheck all yearsTwo or More Races Students [Public School]

Notice how government racial categories have changed in the last 5 years?

8. Click on the Green button at the bottom called Select Filter.

9. Select filter by individual state, and pick your state.

10. Select the button at the bottom “Create Table”

11.  Click on Export Excel and the File Download – Download Zip File.

Screenshot 2015-03-04 15.25.23.png

If you messed up, you can select the button “Modify” to change your criteria.

12. Unzip the file and open the Excel Document.

13. Using the toolbar, Increase the display size to 125%.

14.  Place your cursor on table column names (should be row 7) and then select Data –> Filter.  This lets you filter by town.

15. Select Sort, and sort first by “location city,” sort from A to Z, and then select the + sign and sort by school level code.

This should isolate the high schools in your city.

16. In a new excel file, copy and paste the data rows of high schools in your city and the heading row.

17. Save the excel file.

19. Insert TWO columns after White Students 2012

Call the columns “% White 2012”, “% Minority 2012”

20. Insert FOUR columns after White students 2007.

Call the columns

  • “% White 2007”
  • “% Minority 2012”
  • “5 year change White students”
  • “5yr change Min Students”

21. Calculate the % White students in 2007 and 2012.

SAMPLE FORMULA:   =White Students [Public School] 2012-13/Total Students [Public School] 2012-13

20. Calculate the % Minority students in 2007 and 2012.
SAMPLE FORMULA:  =1-% White Students

21. Calculate the change in percentage of white and minority students.

SAMPLE FORMULA:  =% White 2012-% White 2007

22. Do your numbers make sense? Check your work!!

23. Write a 2-3 sentence narrative interpreting the results, and email to me.

24. Save and send to your partner!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s