Lately, I have been making one or two quizzes a week using Google Forms. I send these to my students to take after we wrap up a unit. They are short quizzes which assess whether they learned and can apply the grammar or vocabulary I was teaching. I then look for commonly missed questions to readdress back in the classroom. I usually ask several open-ended questions in which students must make a sentence. I also choose sentences that use the grammar/vocabulary point well, use it poorly, or have another common mistake. After grading the quizzes, I share them back to the students. Students can see their answers, their graded answers, the correct answers, notes about the correct answers, and the class average for each question. In addition, I leave comments on students’ open-ended answers to help them understand their major or minor mistakes.
Except for open ended responses, the quizzes are self-grading. This makes quizzes on Google Forms quick and convenient for teachers. I was inspired to try this after reading this post by Andy Wolber at TechRepublic. Making the quiz is super easy, but making it self-grading takes a small bit of work. After doing it a few times, it too becomes pretty easy. Google Forms saves its results in a spreadsheet, and then all that is required are some spreadsheet functions (the same that would be used in Excel) to grade and reorganize the data.
Below, I will take you step-by-step through how I do this, including how I share it with students without exposing their names or student numbers to each other. Before starting, see my example here.
Step 1. Make a Google Form
Making Google Forms is really easy, so I am not going to explain this part. If you need help, a quick search will reveal videos and guides to help you.
Step 2. Take the Quiz Yourself
This ensures it is working correctly and gets you the correct answers on the form so you can have an answer key.
Step 3. Make an Answer Key
Make a new worksheet called “Answers” or “Key” or any other name, but no spaces. Rename the “Form Responses” to “FormResponses” or “StudentAnswers” or any other name, but not spaces. In “Answers”, you can either display the correct answers horizontally in columns (minimal work, less organized, many questions) or vertically in rows (more work, more organized, less questions).
Display Answers Horizontally
In the first cell of “Answers”, type =StudentAnswers!A1 to copy the A1 cell. You can now copy this cell and drag it across the entire row. Hit paste, and all the questions from “StudentAnswers”, row 1 will be copied. You have now copied all the questions. Move down a line to “Answers”, row 2 and paste again. All your answers (the first form entry) will be copied as well. You can now select any columns you don’t want, such as the timestamp, and delete them. You should be left with only questions and answers.
Display Answers Vertically
In the first row of “Answers”, type =TRANSPOSE(StudentAnswers!A1:_2) to transform rows into columns. A1 represents the first cell of row one in “StudentAnswers”. The _ should represent the last column of data in row 2. For example, if my quiz requires a student’s name and then has 10 questions, it will be 11 columns long, plus 1 for the timestamp (total, 12 columns). My data would end in the L column. My questions would be on row 1, and my answers on row 2. So, I would type =TRANSPOSE(StudentAnswers!A1:L2) and that would put all the questions in the A column and all the answers in the B column. Therefore, questions and answers would be side by side.
Copy the newly created columns. Right click on the A1 cell and select Past Special > Paste values only. Delete any rows that are unnecessary.
Step 3. Set Up Answer Page
Delete any unnecessary rows so you only have the questions and answers. Here, if you want, you can offer explanations of answers in a new column. If any answers have an alternate, create a new column and add them where appropriate.
Step 4. Set Up Self-Grading Sheet
Create a new sheet, “Graded” (or another name). In the first cell of “Graded”, type =StudentAnswers!A1 to copy the A1 cell. You can now copy this cell and drag it across the entire row. Hit paste, and all the questions from “StudentAnswers”, row 1 will be copied. You have now copied all the questions. Delete any unnecessary columns. If you want any data brought over from the original form that will NOT be graded, such as a name, student number, etc., use the function =StudentAnswers!ColumnRow to copy over your cells in row 2.
Working in row 2, under your first question use the function =IF(StudentAnswers!ColumnRow=Answers!$Column$Row,”Correct”,”Incorrect”). StudentAnswers!ColumnRow would be the student’s first answer in row 2 (eg. D2). Answers!$Column$Row is the correct answer in the “Answers” sheet (eg $A$2). The $ symbol means that, as you copy and paste this formula to other cells, it will still point to the correct answer. “Correct” means that if the answer is correct, the word “Correct” will display. “Incorrect” does the same. If you want to use points, replace “Correct” with 1 (no quotes) and “Incorrect” with 0 (no quotes). When you are finished, press enter and the desired result should display. If it is correct, you can paste this cell along horizontally and all of your answers will be scored. If this works out, paste vertically and your students’ answers will be graded as they are submitted!
Dealing with alternate answers is easy, so long as there are not many of them. In the following example, the student’s answer is in StudentAnswers!A1, the correct answer is in Answers!B1, and the alternate answer is in Answers!C1:
This formula checks to see if the student’s answer matches my answer in B1. If it doesn’t, it checks C1. If it still doesn’t match, it prints “Incorrect”.
Open Ended Answers
Use the simple copying formula, =StudentAnswers!B2, where B2 represents the open-ended answer. Then, working in the “Graded” sheet, you can simply type “Correct” or “Incorrect” (or 1 or 0) over their answer and it will be counted in their total and the averages. This does not affect their original answers in the “StudentAnswers” sheet.
Step 5. Totals
In your “Graded” sheet, you will want a new TOTAL column. If you used points (1 point for correct, o for incorrect), you can use a simple SUM function: =SUM(B1:K10). B1 represents the starting point, B10 represents the ending point. Change them as necessary.
If you used the Correct/Incorrect marking system, you can use the COUNTIF formula: =COUNTIF(B2:K2,”Correct”). This formula counts how many times “Correct” appears in the desired range.
You can make this a percentage of 100 by simply adding /10*100 after each formula. Replace 10 with the number of questions you had.
Step 6. Disguising Student Numbers and Names
This step depends on the information your required on the form. I usually delete the name column and keep only the last four of the student number. To disguise the student numbers, you can use the CONCATENATE function: =CONCATENATE(“*****”, RIGHT(C2,4)). ***** represents what you want to be displayed instead of the numbers. RIGHT represents where the function will begin counting. the 4 represents how many digits from the right will be kept. C2 points to the student number. After concatenating all the student numbers, I copy the newly disguised ones, right click, paste special > paste values only. Then, I delete the original column.
Step 7. Averages
Averages are useful for both students and teachers. You can either show total score averages, so students can see if they performed above, at, or below average, or per question averages so you can see which questions were the most difficult.
Total Score Average
If you want to display the average score in the class, decide where you will put this information (maybe next to every student’s total), then use the AVG function: =AVG(F2:F20). F represents the TOTAL column. 2 is the the first score, 20 is the last. The average score will be printed. If you are using “Correct” instead of points, you can use the following formula: =COUNTIF(Graded!C$2:C$49,”Correct”)/COUNTA(Graded!C$2:C$49)*100. COUNTIF counts how many times “Correct” appears, and divides it against COUNTA which counts how many cells are not empty in a range. *100 gives you a percentage.
Per Question Average
I usually use per question averages to determine which questions need to be readdressed. I put the averages next to the answers on the “Answers” sheet. You can follow the same formulas above, just remember to use a range of cells in a single column (a single question) on the “Graded” sheet.
Step 8. Share Back with Students
After grading the quiz, getting the averages, deleting names, and disguising student numbers, you can now share back your spreadsheet with students so they can check their scores. Leave comments on any open-ended answers that need them using Google’s comments tool.
That’s it! I know that it seems like a lot of work, but after the first or second quiz, it will be quite easy. Hopefully I have explained my steps well. If I haven’t and you are still interested in my method, leave me a comment and I may make a screencast of this process. Let me know how your quizzes go. Happy quizzing!