Student Data Spreadsheet

Student Data Sorted By Strand

I opted to use Google Spreadsheet
to make my student data spreadsheet.  I have used various spreadsheet programs before but I do not create them often.  I think it would be difficult for teachers to dedicate the time to using a spreadsheet like this unless the information was already available online and could be merged or even cut and pasted into a program.  I find the number of formulas available to be overwhelming at first.  It took me a while to figure out how to use a =countif formula to determine the number of correct responses per question.

Support using formulas can be found at this link.

I also wanted to sort the data by Strand.  I decided that having it sorted that way was going to be more helpful to the teacher than just the question number.  In an attempt to make this stand out more, I opted to highlight by strand also. I also alphabetized the list which will make it easier for the teacher to find her student by name.  Any additional students who are added to the list will be alphabetized also.  This is done by sorting the range highlighted.  It's important not to sort the entire page by mistake.

This is a screen grab of the Data drop down menu for sorting a range.  Simply highlight the range that you want sorted.

Many people are not used to looking at spreadsheet so color coding can be helpful.  I chose red for the incorrect responses.  This gives a visually quick look at the overall question.  This was accomplished by using a conditional formatting.  The formatting functions found here are extremely helpful for sorting and extrapolating data.  Conditional formatting is like an "If, Then" question.  I formulated it by saying that "If the text is exactly B (or what ever the correct response was) Then the print was in Black.  Then you have to add, "Text does not contain B (or whatever the correct response was) Then print was in Red.

I might also have noted questions that gave a consistent incorrect answer (i.e. 80% of the incorrect responses answered "B".)  This information could be helpful to a teacher who is trying to determine why a student answered incorrectly.  This would be most helpful in specifically reteaching a particular point.

In this particular data, there were 2 questions that over 80% of the students answered incorrectly.  This type of information helps to inform the next steps taken by the teacher, including when to refocus and reteach concepts missed by that many students.  I could see how breaking down data can help to inform instruction within the class, grade or school.  At our school we often spend staff meeting time extrapolating information out of standardized testing scores.  This can be cumbersome but in many cases allows us to target specific areas that need additional support.  At the classroom level, this information could be collected from summative assessments in an attempt to make sure that the students fully understand the concepts being taught.