Are you a teacher or trainer? Someone involved in a hiring process? Do you need to check somehow that other people have the skills or knowledge that you expect them to? Well, you’re going to love what you can do using Google Forms. You can create a self-grading test for whatever your purposes are. That’s powerful!
Google Forms are amazing tools, allowing you to do some really advanced tricks with forms as the front-end and spreadsheets at the heart. Once you get started you’ll be amazed at just how much you can achieve with this basic premise. Today we’ll look at how to create a self-grading quiz using Google Forms. From there, you’ll no doubt find a lot more ways to use forms and spreadsheets.
Create A Self-Grading Quiz
Creating a form in Google Forms is very self-explanatory: you open Google Docs, create a new form and get typing (or read this if you want a general guide to using Google Forms). The essence of what you need to know is not in the form, but in the spreadsheet of results.
For this example, we have used one form question to ask for the person’s name and written out 5 spelling test “fill in the blank” questions. You can try the same in order to test things, or make up something of your own.
You will need to click on “Choose Response Destination” and tell the form to put responses into a spreadsheet. Then click on “View Responses” to see the spreadsheet. This is where the fun begins.
In the Form Responses tab, you need to use a couple of lines to add the answers and the value for the answers. It also makes sense to include these in the frozen rows. You do need to list the value for the answers in order to make this work, then add up the values in order to calculate your percentage. I weighted all of mine with 2 marks each, but you can use whatever you like.
Now, create a new tab for your test scores, so you can cross reference each person’s answer with the results. Create titles for Name, points and percentage. Reference the question names from the Form Responses tab for the titles. In the first line, reference the cell of the name of the first person responding. Use an “if” statement to determine if the first answer was correct and how many points to assign if it is. My IF statement is as shown below. Copy that formula to the rest of the answer cells. Then, use SUM to add up your points and calculate the percentage in the correct field. Create a percentage score for the first person by dividing their score by the maximum number of points (which will need to be an absolute cell reference using $ signs).
Here’s my IF statement. View the following image to make sense of the cells I’m referencing.
=IF('Form Responses'!B4='Form Responses'!B2,'Form Responses'!B3,0)
This means that if the cells don’t match, the score is 0. So, if the student skips the question, they receive no grade for the question.
Finally, copy this row down to allow for as many responses as you are expecting. Voila! You’re done.
Use A Template
If you’re unsure of your spreadsheet skills, or just want to cheat a little to set things up quickly, check out the templates in Google Docs by choosing New > Form Template. If you do this from within a form, the templates will be filtered to show you form templates. If you do this from elsewhere, just filter the templates to show forms yourself or head straight to searching for “Self Grading Quiz”. There are hundreds to choose from, so just grab a highly rated one and modify it to suit your needs. Or, you can copy and paste the spreadsheet cells (from the template) to a new sheet in the response spreadsheet of one of your own forms.
Change Colours With Rules
If your test is something more difficult to grade, such as a paragraph of text as a response to a question in an English class quiz, you can use colour to help you quickly discern whether the answer is correct and to what degree it is right. Obviously you’ll still need to read the answer yourself, but the colour indicator will help you to grade the class fairly. This works by using the “Conditional Formatting” feature of Google Spreadsheets. You set it to change colour based on words it finds in the cell’s text. So, an answer containing the word “love” could be yellow, while a cell containing the words “love”, “Darcy” and “confused” could be green.
Potential Issues With Google Forms
If the form is refreshed while someone is filling it out, it will lose all the answers they’ve given so far. However, if the student suffers from connectivity problems and waits before submitting they will not lose their responses. If a student submits twice, the teacher will see two entries for their name, each with an individual timestamp.
Resources For Teachers
Now I’m sure you can see just how useful Google forms can be for teachers, trainers and other sorts of evaluators, such as HR staff and recruiters. This could make your life so much easier! Teachers may also like to read about using Twitter for the classroom, using the customisable learning resource Quizdini, and taking advantage of the Wolfram Education Portal.
What other tools do you use for self-grading quizzes. How do they compare to this method? Will you give this a go?