Self-grading multiple-choice tests with Google Docs

I don’t have much love for multiple-choice tests — either for administering them or for taking them — but as a teacher, the format was sometimes required by my administration, and was sometimes useful for simple prove-that-you-read-it quizzes. Google Docs didn’t help me love them, but it did help me never grade them.

Here’s how:

(1) Go to Google Docs. If you don’t have a google account, you’ll have to set one up, but if you already use GMail or GReader or any of the other apps, you just need to log in.

(2) In the upper-left corner, open the New drop-down menu and select Form.

(3) You should now have a blank form. This will be your quiz. Give it a name where it says Untitled form and any additional text that will help your students (your name, class name, instructions, or whatever) in the box beneath it.

(4) Open the selection box for Question Type and select text. Next to Question Title, type “Name:” and check the box that says Make this a required question. (You might want to add a question for class period or ID number, too.  For me, a name was usually sufficient.)

(5) At the top-left of the page, click Add question and select Multiple choice to create the first test question. (You can choose others types of questions, too, of course. I often had a few open-ended paragraph response questions, but these require old-fashioned review and grading.)

(6) Type your question in the box next to Question Title. I recommend numbering it there, too. For example: “1) What is 5+5?” Add some Help Text if you want to (it’ll appear smaller and lighter beneath the question).

(7) Type the first possible response to the question in the Option 1 box. Click add ‘Other’ to add additional options, or just click in the “ghosted” second option to make it appear, and do the same thing again to add as many choices as you want. Be sure to include the correct answer as one and only one option!

(8) At the top-left of the page, click Add question to create the next question. Repeat steps (6) and (7).

(9) Repeat step (8) to make all the questions you want. If you want to change one you’ve already made, hover the mouse over it so that the pencil icon appears to the right, and click on that icon. You can also drag questions up and down to rearrange their order.

(10) When you’re finished, click Done on the active question, and click save on the top-right. If you click on More actions and Edit confirmation, you can edit the message that is displayed after the completed quiz is submitted. (Or you can leave it with the default message.)

(I recommend NOT clicking on any of the radio buttons, because if one is selected when you make the form, it’ll be selected by default for your students (or whomever) on the quiz. If you select one accidentally, make a new answer choice, select its radio button, and then delete it with the x button to its right. Be sure to save.)

(11) You should now have a link to the published form on the bottom of the page. You can copy and paste it into a mass email to your students, or link to it on a school web page (on Google Pages?), or make it more manageable at tinyurl.com and write it on the board. See the example I’m making right now (even take the very easy quiz) over here.

(12) Before any students take the quiz, take it yourself. Enter “ANSWER KEY” (or whatever you like) as your name and all the right answers. Click Submit.

(13) Now go back to Google Docs. You should see your quiz as one of your saved documents. Click on it and you’ll find all responses in a spreadsheet. The date and time of each response is stamped in column A (so you can have deadlines if you want them, and students can’t fake it), names in column B, and all of your questions in subsequent columns. (See what I mean in this example.)

(Once the deadline passes, if you don’t want to accept late submissions, you can click on More actions and select Stop accepting responses.)

(14) All the way on the left, where rows are numbered, between 1 and 2, you should see a gray rectangle. Click on it, and drag it down until it’s between 2 and 3. You should now be able to scroll down over all your students’ responses while the questions and the correct answers remain visible on the top for easy comparison.

(15) There should be a similar gray rectangle to the left of the A above the first column.  Click it and drag it to the right until it’s between the B and the C. You should now be able to scroll left and right through all of your students’ responses and still see the time of submission and the student names to the left.

Now, if you want Google to do all the grading for you (and of course you do), it gets slightly trickier. But it’s not too hard, and after you’ve done it once, it’s easy.

(16) Once a student has submitted their responses, click on the cell in the same row as their responses but to the right of the last one.  So, for instance, since the last question of my sample quiz is in column D, and my first student response is in row 3, I’m clicking on cell E3 (same row as the student, next column to the right after the last question).

(17) In the cell, enter a formula like this one: =arrayformula(sum(C$2:D$2=C3:D3))

Yours will be a little different from this one. The = sign indicates that what follows is a formula. The arrayformula() function indicates that whatever is wrapped inside its parentheses will have array inputs; the sum() function takes a sum of the arguments in its parentheses (or in this case, the number that return “true”); and the last stuff is the array that we’re actually counting.  

If none of that made sense, don’t worry. This is what you need to know: the C$2:D$2 means that the correct answers are in the cells between and including C2 and D2. If your quiz has many more questions, and the answers go from C2 and ZZ2 (or whatever), you’ll need to change that part of the formula to C$2:ZZ$2. Don’t forget the dollar signs! (They’ll be explained below.)

The C3:D3 means that the answers of the student whose answers are in this row are in the cells between and including C3 and D3. You’ll want to make these letters match the letters you used in the previous paragraph, since if the answers go from C to ZZ for the correct answer row, they should for the student, too. You’ll want the numbers to be one higher than what you used in the previous paragraph, since this student’s answers will be one row beneath the correct answers. Make sure you don’t have the dollar signs here!

So the formula sum(C$2:D$2=C3:D3) is asking how many cells between C2 and D2 are equal to the corresponding cells between C3 and D3. (We need to wrap all this in the arrayformula() function since these ranges of cells make up array data.)

If you don’t understand or care about any of this, just enter “=arrayformula(sum(X$#:Y$#=X@:Y@))” (without quotation marks) in the cell, replacing X with the column letter of the first correct answer, Y with the column letter of the last correct answer, # with the number of the row with the correct answers, and @ with the number of the row with the first student’s answers (probably one greater than the number of the row with the correct answers).

(18) Now, once you’ve entered that formula and hit enter, the cell should have the total number of that student’s answers that match the correct answers. When that cell is highlighted, there should be a little blue square in its lower-right corner.  Once all the students have submitted their answers, click on that square and drag it down to the last student’s row. This will copy the formula you entered into each row. (And since we had the $-sign in C$2 and D$2 (or wherever the answers were), those cell locations will be unchanged in all the new formulae; since we didn’t have the $-sign in C3 and D3 (or wherever the first student’s answers were), those cell locations will automatically increment for each row.)

(Unfortunately, you do have to wait for students to submit the answers before you drag these formulae down. If you drag them down into blank rows, the form will recognize that those rows are already in use and subsequent submissions will skip them.)

(19) If you want Google Docs to automatically calculate percentages, click on the cell to the right of the first student’s “total correct” cell (in other words, to the right of the one we just added a formula to). Enter an = sign, click on the student’s “total correct” cell, enter a / sign, and enter the total number of questions. So it should look something like this: =E3/2 . This divides the student’s number of total correct answers by the number of possible correct answers. When you hit enter, the cell will have a 1 if the student got 100%, a 0 if the student got a 0%, and a decimal for anything in between. If you click the Edit tab, and, while the cell is highlighted, click the Format drop-down menu, and then select one of the percentage options, the cell will be displayed as a standard percentage. You can do the drag-down procedure as before to copy this formula for all the other students as well.

It should look something like my example here. Once you know how to do this, it can save an enormous amount of time. I kept a blog for each class period, and posted the agenda and assignments every day. I would sometimes make a “take home quiz” like this and post the link. Other times, when I was required to give a multiple choice test at school, I would make it like this and administer it in the library or computer lab. And, of course, this same method can be easily modified for regular gathering of contact information on the first day of school (bonus: student email addresses will be copy-and-pastable), for learning-style inventories, for parent surveys, or just about anything else.

And I saved all that time for grading essays!

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website

Notify me of follow-up comments by email.

Notify me of new posts by email.