If you use Google Forms for formative assessment quizzes here are some tips for managing the resulting spreadsheet.
Ask for Student ID Number (SID)
When organizing data it is important that your data match. Trying to sort data by student name can be sketchy when students type in their own name. They might use “Robert” one day and “Bob” another. They may simply misspell their name. While I do not need the students ID number it is harder for students to get creative in how they fill it out. This makes it much easier to sort and organize the results.
Rather than ask for “name” make 2 questions. One for “First Name” and one for “Last Name.” This way you can easily sort the resulting spreadsheet by either first or last name. This is also very handy when putting the results in the gradebook.
From the edit screen of the Google Form you want to “View Responses” in the toolbar in order to open the spreadsheet of data. (The first time you will want to leave the defaults in the pop up window and click on “create.”)
After creating your Google Form go to the live form and take the quiz. For first name put “Answer” and for last name put “Key.” Fill out the form with the correct answers. This makes the first line of data in the spreadsheet the correct answers.
By default the first row of the spreadsheet is frozen. This allows you to scroll through all of the student answers and still see the questions. If you were the first person to answer the Google Form quiz and you used “Answer Key” for the name, then the second row contains the answers.
On the left hand side, between rows 1 and 2, hover over the freeze bar. You will notice the cursor turns into a hand. Click down on the freeze bar and drag it down to freeze the first 2 rows. This will freeze the answer key at the top of the spreadsheet. As you scroll through the student answers you can compare their answer to the answer key easily.
Sort each question. This will allow you to cluster which students answered what. Since the results of a Google Form are instantly in the spreadsheet as students submit you are able to quickly identify which students answered each answer choice for a particular question. Divide up the students by who answered what to provide a differentiated activity.
Multi – Column Sort
You probably want to sort by multiple columns. Such as by Period, Last Name and then First Name. There is a blank box to the left of column A and above row 1. I call this the “awesome box.” Right click on the “awesome box” to reveal a list of options for the entire spreadsheet. Choose “Sort range…”
Check the “Data has header row” checkbox. Choosing “Add another sort column” allows you to sort by multiple columns at once. The order matters. If you want to sort by Period and then Last Name you need to sort by Period first and Last Name second.
Conditional formatting allows you to color code the cells based on the answer. For example if the student put down the correct answer the cell background color will be green. If a score is less than 70% you can have the background color be red. This allows you to easily identify trouble areas and find selected answers quickly.
There are a variety of ways you can test the data for the conditional formatting. If you have an exact answer you are looking for choose “Text is exactly” from the drop down menu. If you are testing numbers you may want to choose something like “Greater than.”