Suburban District 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 suburb changed over the last 25,  years?

1. Go to http://nces.ed.gov/ccd/elsi/

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 2013-14,  2008-09, 2003-4, 1998-1999, 1993-94.

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

Information>>Contact Information select

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

6. Under Enrollments select all 5 choices

Total Enrollment

Check or uncheck all yearsTotal Students [Public School]
2013-14 2008-09 2003-04 1998-99
1993-94

AND

Students in Special programs – select all years

Check or uncheck all yearsTotal Free and Reduced Lunch Students [Public School]
2013-14 2008-09 2003-04 1998-99

AND

Enrollment by Race/Ethnicity

Select all years

  • Check or uncheck all yearsAmerican Indian/Alaska Native Students [Public School]
    2013-14 2008-09 2003-04 1998-99
    1993-94
  • Check or uncheck all yearsAsian or Asian/Pacific Islander Students [Public School]
    2013-14 2008-09 2003-04 1998-99
    1993-94
  • Check or uncheck all yearsHispanic Students [Public School]
    2013-14 2008-09 2003-04 1998-99
    1993-94
  • Check or uncheck all yearsBlack Students [Public School]
    2013-14 2008-09 2003-04 1998-99
    1993-94
  • Check or uncheck all yearsWhite Students [Public School]
    2013-14 2008-09 2003-04 1998-99
    1993-94
  • Check or uncheck all yearsHawaiian Nat./Pacific Isl. Students [Public School]
    2013-14 2008-09
  • Check or uncheck all yearsTwo or More Races Students [Public School]
    2013-14

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

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

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

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

10.  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.

11. Unzip the file and open the Excel Document.

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

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

14. In a new excel file, copy and “paste special” (Values only) the data rows of schools in your city and the heading row.

15. Delete schools that are state technical schools – these enroll students beyond the city boundaries and will likely have demographics significantly different than the town schools.

16. Save the excel file.

17. Insert TWO columns after White Students 2013-14

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

18. Insert FOUR columns after White students 2008.

Call the columns

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

19. Calculate the % White students in 2008 and 2013.

SAMPLE FORMULA:   =White Students [Public School] 2013-14/Total Students [Public School] 2013-14

20. Calculate the % Minority students in 2008 and 2013.
SAMPLE FORMULA:  =1-% White Students 2013/14

21. Calculate the change in percentage of white and minority students between 2008 & 2013.

SAMPLE FORMULA:  =% White 2013-% White 2008

22. Complete the instructions to calculate the 10, 15, and 20 year % changes in minority and white students.

23. Complete the instructions to calculate the % free and reduced lunch eligible students for each 5 year interval, and the difference over 5, 10, 15, and 20 years.

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

25. If you notice growth in a particular racial/ethnic category, you may also want to create separate columns to isolate and measure that difference .

26. Save and send to your partner!

Advertisements