We now have some pretty incredible skills at our disposal. Let’s recap what we can do:

- Input data into a spreadsheet
- Calculate formulas like we’re Einstein
- Filter data to pick out what we want to see
- Graph data to make it easier to visually draw conclusions
- Sort data alphabetically as well as numerically
- Interpret data like a pro

We’ll put our skills together in this lesson. We’ll take a look at a spreadsheet and methodically use our new skills to make sense of it all. To get started, you know the drill; select your spreadsheet software here:

Google Spreadsheets

Microsoft Excel

## Exercise 1: Filter to understand gaps

Lucky for you, Mark made a video in which he expertly walks you through the steps of this exercise. (Google Docs is used here, but the video should be useful even if you use Excel.)

Download and open this CSV file. Now let’s analyze the data step-by-step.

Step 1. As you open the spreadsheet, DON’T PANIC! We know this spreadsheet may look like a numerical octopus with formulaic tentacles coming to get you. But don’t worry; we’re going to conquer it!

Step 2. What are we looking at? The title says “US History Final Exam,” and 103 students took this assessment. There were 25 questions with A, B, C, or D multiple answer choices. We can see what percentage of students answered each question correctly and incorrectly. We also know there are five content areas assessed.

Step 3. We see a mess of numbers and letters; how do we make sense of it? Often times a good filter will cure a case of excessive information. Looking through the different categories, one in particular stands out as a useful filter. “Content Area” would help us determine which concepts students grasped or struggled to learn. Let’s look at each one individually.

Step 4. Since we want to filter content area, let’s select that cell and then click the filter button. It looks like this in case you’ve forgotten since that lesson:

Step 5. Take time to look at each content area. Maybe write or type yourself a quick note if anything jumps out at you.

Step 6. What did you notice? Content Areas 2-5 seemed to have a group consensus on the correct answer. But Content Area 1? Oh dear! Our class couldn’t truly identify the correct response (especially question 15!).

Step 7. What does that mean? Given that we had 103 students take this exam, we can be safe in making interpretations. This is because we have a large amount of data. It appears as if Content Area 1 had hotly contested questions. As a teacher, you might take a look at those four equations again. Were there any vague instructions or similarly worded answer choices? Were you absent for the days when this content was taught? This data analysis helps us understand that we can get the most bang for our buck by improving this specific area.

## Exercise 2: Sort, graph, calculate, interpret!

Download and open this CSV file. Again, step-by-step:

Step 1. Sort the final exam data from highest to lowest by clicking the cell “US History Final Exam Grade” then clicking the “A/Z” button nestled between the fancy looking “E” and the filtering button. It automatically sorts it from least to greatest. If you click again, the spreadsheet will sort it from greatest to least.

Step 2. Let’s see how many students scored within different proficiency levels. Type “=COUNTA(” into the cell next to the first “100” score and drag your mouse to select every score that achieved a 90 or above. 33 students earned an “A.” Hooray! Repeat the process to determine the number of students earning a “B,” “C,” and below a 70.

Step 3. We see there are 25 students who earned a “B,” 22 students who earned a “C,” and 23 students who did not earn a traditional passing grade. Let’s make a column chart to visually analyze this data. Write your values as depicted in the screenshot below:

Step 4. Drag your mouse over all eight cells so they are all selected (they’ll be highlighted blue when this happens). Select the “Insert” option, “Chart,” and then the option “Column” on the left side of the Excel window. Now click the “Clustered Column” button.”Series1,” you strike again! Right-click, “Select Data,” and change the “Name” to “US History Final Exam Grades.”

Step 5. What do you notice? Looking at the graph, we see the grades were predominantly “A’s” (hooray!), but the students not earning passing grades is roughly equal to those earning “B’s” or “C’s” (oh no!). Given what we know from our first exercise, you could do a targeted reteach workshop with the students who didn’t earn a passing grade focusing on the content area in need of the greatest improvement (content area 1).

Step 6. Calculate the mean score among the 103 students. Type “=AVERAGE(” in any cell, select all of the numerical final exam grades, then close your parenthesis and hit enter. The mean score will be about 80.2.

Step 7. Calculate the median score among the 103 students. Type “=MEDIAN(” in any cell, select all of the numerical final exam grades, then close your parenthesis and hit enter. The mean score will be 80.

Step 8. What meaningful observations can we make? The mean is an appropriate measure of the center of our exam grades because we have so many data points. Not by coincidence, we also see that the median and mean are similar.

Congratulations on working your way through this “capstone” course. You incorporated everything you’ve learned and are now well on your way to reclaiming your classroom!

## Exercise 1: Filter to understand gaps

Lucky for you, Mark made a video in which he expertly walks you through the steps of this exercise:

Open this Google spreadsheet. Select “File” and “Make a Copy” so you can edit this spreadsheet on your Google Drive account. Ensure you are on the first tab entitled “Question Analysis.”

Now let’s analyze the data step-by-step.

Step 1. First, as you open the spreadsheet, DON’T PANIC! We know this spreadsheet may look like a numerical octopus with formulaic tentacles coming to get you. But don’t worry; we’re going to conquer it!

Step 2. What are we looking at? The title says “US History Final Exam,” and 103 students took this assessment. There were 25 questions with A, B, C, or D multiple answer choices. We can see what percentage of students answered each question correctly and incorrectly. We also know there are five content areas assessed.

Step 3. We see a mess of numbers and letters; how do we make sense of it? Often times a good filter will cure a case of excessive information. Looking through the different categories, one in particular stands out as a useful filter. “Content Area” would help us determine which concepts students grasped or struggled to learn. Let’s look at each one individually.

Step 4. Since we want to filter content area, let’s select that cell and then click the filter button. It looks like this in case you’ve forgotten since that lesson:

Step 5. Take time to look at each content area. Maybe write or type yourself a quick note if anything jumps out at you.

Step 6. What did you notice? Content Areas 2-5 seemed to have a group consensus on the correct answer. But Content Area 1? Oh dear! Our class couldn’t truly identify the correct response (especially question 15!).

Step 7. What does that mean? Given that we had 103 students take this exam, we can be safe in making interpretations. This is because we have a large amount of data. It appears as if Content Area 1 had hotly contested questions. As a teacher, you might take a look at those four equations again. Were there any vague instructions or similarly worded answer choices? Were you absent for the days when this content was taught? This data analysis helps us understand that we can get the most bang for our buck by improving this specific area.

## Exercise 2: Sort, graph, summarize, interpret!

Click the second tab of your Google spreadsheet from Exercise 1 entitled “Student Scores.” Again, step-by-step:

Step 1. Hover over the column E, select the arrow, and choose the option “Sort sheet Z -> A.”

Step 2. Let’s see how many students scored within different proficiency levels. Type “=COUNTA(” into the cell next to the first “100” score and drag your mouse to select every score that achieved a 90 or above. 33 students earned an “A.” Hooray! Repeat the process to determine the number of students earning a “B,” “C,” and below a 70.

Step 3. We see there are 25 students who earned a “B,” 22 students who earned a “C,” and 23 students who did not earn a traditional passing grade. Let’s make a column chart to visually analyze this data. Write your values as depicted in the screenshot below:

Drag your mouse over all eight cells so they are all selected (they’ll be highlighted blue when this happens). Select the “Insert chart” button (located next to our favorite filter button) and select the option “Column Chart” with blue and red vertical bars. Now click the blue “Insert” button.Label your vertical axis, or y-axis for all the math teachers out there, “Number of Students.” Label the horizontal x-axis “Grades,” and the Chart Title as “US History Final Exam Grades.”

Step 4. What do you notice? Barely any students made “C’s,” right? Remember during our “Creating Graphs” section when we discussed the importance of starting most graphs at zero? Unfortunately Google Spreadsheets doesn’t, so this graph slightly misrepresents the changes from grade to grade. If you click on the vertical axis numbers, there will be an option to input the “Min” and “Max” numbers. Let’s put the “Min” as zero and hit enter. Now we get a nice visual for our grade distribution:

Step 5. Calculate the mean score among the 103 students. Type “=AVERAGE(” in any cell, select all of the numerical final exam grades, then close your parenthesis and hit enter. The mean score will be ~80.2%.

Step 6. Calculate the median score among the 103 students. Type “=MEDIAN(” in any cell, select all of the numerical final exam grades, then close your parenthesis and hit enter. The mean score will be 80%.

Step 7. What meaningful observations can we make? The mean is an appropriate measure of the center of our exam grades because we have so many data points. Not by coincidence, we also see that the median and mean are similar.

Looking at the graph, we see the grades were predominantly “A’s” (hooray!), but the students not earning passing grades is roughly equal to those earning “B’s” or “C’s” (oh no!). Given what we know from our first exercise, you could do a targeted reteach workshop with the students who didn’t earn a passing grade focusing on the content area in need of the greatest improvement (content area 1).

Congratulations on working your way through this “capstone” course. You incorporated everything you’ve learned and are now well on your way to reclaiming your classroom!

## Geeky Extensions

That fancy “E” we discussed earlier? You might know it’s really the Greek letter “sigma.” That symbol stands for “sum” and can be used to add up any data you want. Go add those 103 student exam grades grades together. Your spreadsheet can do it in a split second!