Google in the Enterprise
Use Google Forms to create a self-grading quiz
By Andy Wolber | November 2, 2012, 12:34 PM PDT
Google Forms provides a free tool to create and gather survey data. Web based forms can save a lot of time. For example, you can create your own form to gather contact information or get feedback following a meeting.
A Google Form stores each respondent’s answers in spreadsheet cells, with each answer in its own cell. For example, a 6-question form produces a row filled from Column A to Column F with a respondent’s answers.
To a spreadsheet whiz, these survey responses are fodder for calculations. Count how many people provided an address from a particular zip code or sort responses into “yes” or “no” votes. You can see which respondents selected “C” when responding to a multiple-choice A, B, C, or D question. You can sort, calculate, and process the data as much as you like.
Create a quiz
Survey data can even be used to create a “self-grading” quiz. The process is relatively simple: gather your responses in a Form, and then create a second Sheet to compare the responses to correct values. Correct answers may also be scored to receive varying values: Question A might receive 5 points, while Question B might receive 2 points.
1. Create the form
2. Accept responses
Make sure the form is active and set to accept responses. Open the Sheet created with the Form in your Google Drive, then make sure “Accepting Responses” in the Form menu is checked.
Make sure your form is set to accept responses
3. Create a new spreadsheet page
Create a second Sheet within your spreadsheet document. Click on the “+” icon in the lower left corner to add a new Sheet. This adds a new spreadsheet to your document. This is the sheet we’ll use to create a row with the correct answers, and to compare responses to those answers.
Add a second Sheet to use for grading
4. Create the “answer key”
The simplest way I’ve found to create an “answer key” is to take the quiz myself. From the Form menu, choose “Go to live form”. This opens the quiz. Complete the quiz with the correct responses. After you click “Submit”, you’ll see the confirmation page. You can close this tab/window and return to the Form’s spreadsheet.
Create the answer key by filling out the form with the correct responses
The correct answers should now be displayed in your spreadsheet. We’ll need to copy these to our answers / grading sheet. Click on the row number (#2 in my example) where the answers are displayed. This selects the entire row. Choose “Edit - Copy” (or Ctrl-C) to copy the row.
Copy the correct responses to the answer sheet
In the bottom left, click on “Sheet 2″ to change to the spreadsheet where we’ll be doing the grading. Click on row number 1 and choose “Edit - Paste”. We now have the correct answers listed in Row 1 of Sheet 2.
5. Compare responses
To compare a response to the correct answer, we’ll use a spreadsheet formula. If the response matches the answer then we’ll return points. If the response doesn’t match the answer, then the points will be zero.
Compare responses to the correct answer
For our first cell, we’ll use the following formula in cell B2 on Sheet 2, our grading sheet:
Read this as follows: If the contents of Sheet1!B3 equals the answer found in Sheet2!$B$1 then return a value of 5 points, otherwise return zero.
The $ symbol in the Sheet2!$B$1 is essential. It ensures that when you copy and paste the formula into another cell, the item will still point to the correct answer. Depending on your survey and cell setup, the specific cells may vary. The basic idea, though, is to compare an answer cell to a response cell.
Similarly, for the second cell, we’ll use the following formula in Sheet 2, cell C2:
Note how we adjusted the point value of this second question to return 2, instead of 5.
Next, we created a column for total point values in Column C. The formula here is:
With a long quiz, simply use the first and last cells containing values in the row as your starting (e.g., B2) and ending (C2) values. We now have a Total Points column.
6. Copy grading row, as needed
Select your now completed self-grading row (in our case, Row 2 on Sheet 2) by clicking on the Row number (e.g.), choosing “Edit - Copy”. Then paste the “self-grading” row into as many rows as needed. For example, if you’re anticipating responses from 20 people, make sure your formulas fill at least 20 rows of your spreadsheet.
In our case, here’s what our responses look like, stored on Sheet 1.
Sample complete responses
And, the corresponding graded answers, which are stored and calculated on Sheet 2.
Sample complete grade sheet
Google Forms “Quiz” tips
Self-grading in Google Forms works only with answers that can be an exact match, such as multiple choice responses. It won’t work with free-form text or paragraph fields. You can certainly still use a Google Form for a test that includes essay responses, but those will have to be graded manually.
In a real-world quiz, I recommend you include a required “Name” field with a text format on your form. This way, you’ll capture the name of the respondent. If you use this in a class or testing setting, I suggest you make ALL fields have a required response. This ensures that someone doesn’t accidentally skip a question.
Finally, make sure that respondents know they’ll need to keep their browser tab or window open. If you use a form for a long-format quiz or exam, respondents might accidentally close their browser. You might include a bit of advice on the Form to remind people to keep their browser open.
Google Forms has saved me a great deal of time grading short quizzes. I am still waiting, however, for Google to release a system capable of evaluating essay responses to complex questions such as the “answer to life, the universe and everything“. In the meantime, let me know how you’ve used Google Forms to evaluate or grade responses.