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 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 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
Enrollment by Race Ethnicity
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.
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!