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!
Hia,
Great idea!
You don’t have to even wait for students to submit their forms to drag down the formulas. Using two sheets, the first receiving the form and the second doing the calculations, as long as the second sheet references an array on the first sheet much larger than necessary, formulas can be placed for unsubmitted forms and update as quickly as possible.
Cool, thanks for the tip.
Thanks for writing it up, Robert. I can’t wait to begin playing with it. As I write this, I’m flipping out about the potential uses and the amazing power. Thanks too for arrayformula. I think I’ll try it along the bottom edge (or just above the splitter) to see class-wide performance on each question.
So simple, so easy. Wow. Spreadsheet squared, no, cubed.
Very helpful! Thank you.
I think an online quiz functionality for forms with immediate feedback for the students (correct vs. incorrect responses) would be such a helpful addition to the Google School Apps. Surprised one of their prodigies have not yet released this and some sort of online gradebook for parent viewing.
[...] Google forms [...]
I am trying to do a demo online quiz but cannot figure out how google can grade it. I am lost in the directions given above. Can someone please help?
Thanks
Mr. Leven,
The directions above are the way to do it. I might be able to help if you can say more specifically where you’re confused by them.
The test I set up has two questions one in cell C and one in cell D. I have three people that took the test and I am trying to figure out how Google can grade it. I tried using the formula =arrayformula(sum(C$2:D$2=C3:D3)) but get nothing when placed in Cell C. I currently have the answer key in row 2 and the gray box between row 2 and 3. I would like the correct answers to appear in Cell E.
What am I missing?
Can students then see the results of every student when looking back for feedback? Can this be made private?
Thanks,
Ben
Mr. Leven,
The formula ‘ =arrayformula(sum(C$2:D$2=C3:D3)) ‘ (without quotation marks) should return the number of cells between C2 and D2 that match the values between C3 and D3. If C2 and D2 contain your correct answers, and C3 and D3 contain one student’s responses, then that formula should tell you how many correct responses the student gave.
When you say you “get nothing when [the formula] is place in Cell C,” it sounds like that might be where the problem is.
The formula should go in the cell where you want the number of correct answers to appear. If one student’s responses are in cells C3 and D3, and you want the number of correct responses next to them, in E3, you should put that formula in cell E3.
(See steps (16) and (17) above.)
I hope this helps.
Mr. Leven,
I forgot to answer the last part of your question. If you share the spreadsheet with students, then yes, they will be able to see all other student results.
To make a private set of results for each student would require making a separate page of the spreadsheet for each student, and sharing each of them individually. That would not be unusually difficult, but it would be time consuming, and is beyond the scope of this article.
[...] Google Spreadsheets as a Self-Grading Quiz This week I am experimenting with using Google Spreadsheets as a self grading quiz. I found the instructions on this site. [...]
You are never allowed to delete this page! I send the link to my teachers often and this is an invaluable resource! Thank you, thank you, thank you!
rpollack,
Great job on the site. It really was a help. It answered my question of how to grade the tests, but now I have another one. Is it possible to have the test automatically graded and show a percentage or number correct to the student after they take the test? Thank you and I hope to hear back from you.
Shumaker,
I’m not sure that it is possible in any convenient way using the tools Google currently makes available; of course, you could modify the method above such that each student gets his or her own sheet rather merely a row on one sheet, and then you could individually “share” them with the students so that they can see their own online. This would obviously be rather labor-intensive, and less convenient.
Otherwise you could share the main sheet, but then students could see everybody else’s scores. You could, I suppose, solve this problem by having students enter secret ID numbers rather than their names. It would be less convient, though.
What would be great is if Google allowed you to selective share PARTS of a spreadsheet rather than the whole thing, but I don’t think this is possible (yet).
rpollack,
Alright I was just checking. I really didn’t think it was possible. However, now I have another problem. I would like Google Docs to take just the name, email, score, and percentage and put them into the second sheet (so I don’t have all the questions on the second sheet, just names and scores), when they come in. I know it possible, I just can’t figure out how the guy I saw do it, did it. Thank You so much.
Shumaker,
If you create a second sheet (click “add sheet” on bottom left of screen), and navigate to it, you can make the cells there refer to the ones on the previous sheet.
For instance, say I’m on Sheet 2 and I want my A column to reflect the F column on Sheet 1. I put the cursor into the A1 cell, and I type “=Sheet1!F:F” (without the quotation marks) and hit enter. That tells cell A1 to replicate whatever is in cell F1 on “Sheet1″. Now if I click the little square that appears in the corner of that cell when I hover the mouse over it, and drag it down to a lower place in the column, I will propagate that rule down the column and A2, A3, A4 etc. will reflect F2, F3, F4, etc. on the other sheet.
Thank you so much!
WOW! I love it! Thanks for the information, expecially the array formula. I am good at Excel, but have never used that formula before!
Thank you!
Google allows us to publish only the second sheet (when you click ‘Share -> Publish as a web page’ it asks which sheet to publish or all of them ). This form even has the field with ‘All cells’ in it so I guess we can specify the sells but I don’t know how.
Well, like Shumaker I want to publish the second sheet with dates, names, and marks only so that my students could see their grades just after the test. If I use ‘=Sheet1!F:F’ etc they will get their names and dates but not the results. Jon says that we can do calculations in the second sheet automatically. So if we use the formula “=arrayformula(sum(X$#:Y$#=X@:Y@))” on the second sheet and somehow make it refer to the first sheet, the grades also must update automatically. The question is how to change the formula. Thank you!
Andrey,
Try adding (really, prepending) “Sheet1!” to the cell references in the formula. So instead of “X$#:Y$#”, try “Sheet1!X$#:Sheet1!Y$#”.
I haven’t tried this, but that’s how you would refer to the cells on the other sheet. If anybody else has a better answer, please do respond here.
Andrey,
I should have made it clearer that I think “Sheet1!” should be prepended to all cell references, including those on the other side of the “=” sign.
Rpollack,
Thank you! I’ve just tried it and the correct way turned out to be “=arrayformula(sum(Sheet1!X$#:Y$#=Sheet1!X@:Y@))”. But if I write Sheet1! after “:” I get ‘Error’. Anyway, it works now. So, thank you again!
Hey techies,
I need help editing my form. I did it before last summer and now can’t figure out how. After I save the form and go back to it on a different day, I can only view the spreadsheet, but I need to edit it. HELP!
Hey, If you like this stuff try quizstar does a multiple choice and short answer and its ready to go, no formulas, just upload a class list.
[...] subjective test elements, one can set up their spreadsheet to grade students’ work automatically: check this link for how. That’s one thing I could get hooked [...]
up to how many column does your spreadsheet have?
this is a great tools but I need a longer spreadsheet.
[...] more helpful for helping me accomplish this task was the tutorial by Robert Pollack about how to set up the form so that it’ll automatically grade the quiz once everyone’s taken it. I felt immensely accomplished after having successfully followed [...]
Joys,
I’m not sure if there’s any limit to the number of columns in Google Spreadsheets, but you can certainly go on a long, long time, adding more and more columns.
Some really neat ideas in your blog! I am trying to create an online quiz for my biochemistry course using the Googles Form. I would like to create some matching type questions using columns and rows. However, it seems that the number of columns that can be used is limited to five. Is there a way to override this limitation in the Form column format? Thanks / Hanley
I think you’re talking about the “grid” question type. That is indeed limited to five columns, and I don’t think there’s any way to change that.
If you’re talking about something besides that, then I don’t understand. Certainly there is no five-question limit, or any five-column limit within the spreadsheet associated with the form.
[...] 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. [...]
[...] 5. Self-grading multiple-choice tests with Google Docs [...]
Where do you post your quizzes and this form? Do you imbed it on a school website? Or do you email it to students?
Connie
I keep a class blog where I post assignments, and I put links there. But you could just as well put them on a school website or send them in email. Even use a service like tinyurl.com or something and write the link on the board. (Though then surely some kids would write it wrong.)
[...] quiz using Goggle Forms, and it actually self checks. Shirly Smith shared on facebook today the directions for making quizzes that self check, and so we are ready for our kids to come in Thursday after [...]
I wonder if Google Docs would be willing to make a test bank app to drag and drop previous questions into a form. That would be mighty handy. Especially if the questions had meta-tags!
Go to Google Docs>Click New>From Template
Search for Self Graded Quiz….SSB
It has instructions, it is easy to use and can be customized easily.
Great post. I have used GDocs for short quizzes, but I love your self-grading additions. I worry, though, for longer tests I would have trouble monitoring students possibly sharing answers over the computer (chat, email, etc). Any suggestions?
[...] 16.make your own self-grading mult. choice tests in Google docs http://rpollack.net/2008/09/self-grading-multiple-choice-tests-with-google-docs/ 17.Earth science http://earthsci.org/index.html earthquakes [...]
[...] Self-grading Google tests – With a pinch of technical know-how, you too can build tests in Google Docs that will correct themselves! [...]
Thanks for the great instructions!
I was wondering if there were a way for multiple choice questions to accept more than one answer? I thought of a workaround, adding an “other” question that says other possible responses so they could type in any other answers that might be right, but that wouldn’t be as cool as accepting multiple responses.
Then doing the auto grade would be next to impossible I guess with my workaround
I have been trying this again and again and cannot get past the freezing of the rows and columns. After extensive research, it appears that you can no longer freeze rows or columns on the actual spreadsheet where the form responses are collected. This is due to a new functionality added, something like absorb row.
The workaround proposed was creating a new spreadsheet, copying the student responses over to the new sheet once you stop accepting responses, and finishing the setup on the new sheet. Since it is not the sheet where student responses are actually collected, you are supposed to be able to freeze rows and columns. I can freeze the rows in the right place, but I still cannot freeze the columns between B and C. I can freeze between A and B but when I try between B and C my responses get cut off and put way down at the bottom of the spreadsheet and are clipped and some are missing.
Has anyone used this recently, but not using the old version of Docs? Is anyone having problems or can anyone suggest a work around?
Just found this and was able to follow directions to create a self grading quiz. I now will look forward to grading my next MC test. I suspect the students will be less excited. TY! Great job!
does any one know how to import the list of students into the form for report cards?
I tried to apply this to a data collection spreadsheet that I have been working on. It’s almost what I need, but not quite. I have a total of 10 questions with 8 of them having true answers but 2 having a possible no answer (not applicable). How can I adapt this formula to make it look for the possible ‘not applicable’ answers and then change the divider to accommodate for blanks? I would appreciate any help in this as I have been trying to get this done for several days. Thank you in advance for your time.
I have read this article a few months earlier. I did not understand it then. Today I applied to my test and it work great. Thank you very much.
I’m not exactly sure how to do that (or I’m not certain that I understand your situation), but I would try to do is make “not applicable” into one of your multiple-choice options. So then you could still follow the exact same procedure, and there would be no blanks, but your “E” option (or whatever it was) would be “N/A.”
@rpollack and @owen Wouldn’t using check box’s instead of multiple choice options allow you to have more than one option be selected?