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.
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.
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 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
Google Doc Becomes Slides – Magically
I think you will find this a fun activity to turn your Google Docs into Google Slides! Slides allow for multi-media and have some distinct advantages over Google Docs sometimes. However, Docs is easier for brainstorming and outlining. Now you can have the best of both world by turning your Google Docs to Google Slides. Use the Doc to Slides Add-on by Alice Keeler
My Google Docs Won’t Connect to the Internet
I am online but my Google Docs are offline! Help!! Explore troubleshooting solutions for the ‘Google Docs Offline’ issue.
Maximizing Retrieval Practice with Padlet
Discover the power of retrieval practice as a potent tool for long-term learning. Explore how Padlet, an interactive digital platform, can be effectively utilized to facilitate retrieval practice and create an engaging learning experience. Learn valuable tips on structuring, prompting, peer learning, feedback, review sessions, and maintaining variety for optimal results. Maximizing Retrieval Practice with Padlet
14 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?
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?
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.
You must log in to post a comment.