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.

via Use Google Forms to create a self-grading quiz – TechRepublic.