This Post has been UPDATED
Want More Help with This? Become a Premium Member
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.
First Name
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.
View Responses
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.”)
Answer Key
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.
Freeze Bar
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
Hovering over the column indicators will reveal a small drop down arrow. Click on this to choose to sort the sheet. You can sort the results by Last Name, Period, etc…
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
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.
Click on the column indicator of a question to highlight the entire column. Right click on the column indicator and choose “Conditional formatting.”
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.”
Choose the formatting options that will display if the cell has that value. You can add multiple rules.
Example of Conditional Formatting
-
Feedback Made Simple in Google Classroom
Providing timely feedback in Google Classroom is important. Join Alice Keeler for a free OTIS workshop on feedback in Classroom.
-
Translating Your Google Form
Add multiple language translation to each question in your Google Form. Translating your Google Form is fast and easy with Quiz Helper.
-
Harnessing the Power of =TIME()
You have a start and end time, how do you display them together in the format of 8:10 – 8:15???? Harness the power of =TIME()
6 thoughts on “8 Google Forms Formative Assessment Tips”
Fantastic work, Alice. Very innovative.
I use Flubaroo a bit but I like the way you can take control of assessment using your tips.
Do you have any suggestions for having students being allowed to work on the form (assignment) on more than one occasion?
Alice,
Is there a way to create a series or bundle of quizzes for students to complete at their own pace? In our old LMS we were able to create a bundle with a locking feature so that students couldn’t move on to the next assignment/quiz until they received a certain percentage. We are trying to find a way to make this happen using google forms and/or Classroom. Any advice?
Thanks
Google Forms is not the right tool for this. Google Classroom will not allow you to unlock features, but you can make multiple Google Classrooms. So after completing one set of tasks, they can receive the code for the next class.
Love your work! Do you have a form for tracking weekly acquisition of sight words? (Words are already in the form). I am wanting to track mastery of the words from week to week. Sometimes the student correctly identifies a word and then does not 2 weeks later.
Use the same form but install the rowcall add on to the spreadsheet. Will organize by student.