Google

Use Google Forms to create a self-grading quiz

Andy Wolber shows us how to use Google Forms to build an automated quiz using the survey tool.

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

Follow the basic steps in our earlier TechRepublic post, "Use Google Forms to create a survey" to create your 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:

=IF(Sheet1!B3=Sheet2!$B$1,5,0)

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:

=IF(Sheet1!C3=Sheet2!$C$1,2,0)

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:

=SUM(B2:C2)

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.

Also read:

About

Andy Wolber helps people understand and leverage technology for social impact. He resides in Ann Arbor, MI with his wife, Liz, and daughter, Katie.

8 comments
Daolon
Daolon

Interesting event that keeps happening.  I set up the sheets as you described, named Responses and Answers, but when a new response enters the Responses sheet, it deletes the corresponding row that would grade those responses in Answers.  Any ideas on what I may have done wrong?

Sharon_Be
Sharon_Be

I have a question - I hope someone can answer. I am putting together a survey using a Google Form.   I would like to be able to put a new line character in my Questions Title, but I am not sure how to do this.  Can anyone help?

AZEducator
AZEducator

Using Google forms saves me much time taking attendance for 5 sections with over 100 students. Students sign in at the beginning of each class and the spreadsheet automatically adds the date and time. I use the same form throughout the semester and the spreadsheet can calculate how many classes a student attended or not. I can also add additional questions from time to time in order to survey students about a particular topic.

nerskine
nerskine

Have you tried the Flubaroo script for automating the grading? If not you should look at it. We recommend this to all of our teachers when it comes to making quizzes with Google Apps. It's free and automates grading and more. As a tech coordinator in a school district, I appreciate all of the great tips and articles. Keep them coming!

ditodude
ditodude

Nice post. In addition, it is possible to leverage Google Apps Script for more options. On the Google Apps Developer Blog there is an example script called Flubaroo. The blog post is called "Grading Made Easy using Apps Script". Perform a search to find it.

Mark W. Kaelin
Mark W. Kaelin

Andy describes a clever little trick using Google Forms, have you got a trick like this up your sleeve that you'd like to share with your peers here on TechRepublic?

andy
andy

Thanks for the pointer to Flubaroo! I'll look into it! --Andy