I was approached by a colleague wanting to know how to move his multiple choice quizzes online. I first suggested Quizlet or Kahoot (both are good options) but what was lacking was the tracking improvement over time. I believe this is possible with Kahoot if you have a premium account. As it turns out, Google recently added an integrated quiz option into Google Forms!

Some years ago, I came across a script for Google Spreadsheets that “self-marked” a quiz. As a teacher, the idea of “self-marking” is pretty interesting in terms of instant feedback and formative assessment. To see that this is a built-in feature now is quite exciting.

Here’s the step-by-step to create a multiple choice quiz.

Creating Your Quiz

Step 1

Log into drive.google.com and create a new Google Form.

Step 2

Once the form editing window opens, click on settings:

screen-shot-2017-01-09-at-10-29-55-am

Now, click on “QUIZZES” in the settings screen and select any options you want. For example for a formative knowledge-testing quiz, you may want to uncheck “Respondent can see Correct Answers” to encourage students to take the test several times for mastery.

screen-shot-2017-01-09-at-10-30-06-am

Step 3

This next step is very important! Make sure you add a name field on your quiz if you want to track your students’ progress over time. Having the name field as the first option will also mean that when you view your results spreadsheet, the name column will be first.

screen-shot-2017-01-09-at-10-45-50-am

Note regarding protecting students privacy: Since this data is being stored on a non-Canadian server, you do not want students to use their full name. Consider having them come up with alter egos that they can enter or assign every student a number based on your seating plan or alphabetical class list.

Step 4

Lets add another question by clicking on the + icon:

screen-shot-2017-01-09-at-11-05-37-am

I’ve set up a sample multiple choice question. You will want to check off “Required” if you want students to give an answer to every question.

screen-shot-2017-01-09-at-11-07-45-am

Step 5

Before we’re done editing this question we need to click on “ANSWER KEY” to choose the correct answer. Note that it is possible to highlight more than one correct answer.

We can also add answer feedback. You will have an option of adding a different message depending on if the student answered the question right or wrong.

Also, make sure to set how many points the question is worth. I recommend 1 point so we can see a score total at the end.

screen-shot-2017-01-09-at-11-07-58-am

Add as many more questions as you like. It is also possible to add pictures for multiple choice questions (ex. Which one of these pictures is Batman?).

Step 6

When you’re ready to make your quiz available to students, click on the “SEND” button at the top right.

I suggest send via link. Copy the link and paste it to your website or block. Or you can use http://goo.gl to shorten the URL, write it on the board and have students access it on their phones (or school iPads).

screen-shot-2017-01-09-at-11-24-58-am

Taking a Quiz

Once you give students the link to the quiz, it will look something like this for them:

screen-shot-2017-01-10-at-9-51-56-am

Remember you can hide point values in the quiz options if you want to.

Once they have submitted the quiz, we see this screen with a few options:

screen-shot-2017-01-10-at-9-52-15-am

If they click “View your score” we see this:

screen-shot-2017-01-10-at-9-52-45-am

We can turn off the correct answer in the quiz settings (ie. They will know they got it wrong but don’t know what the correct answer is).

Analyzing Results

There are several ways we can view the data.

From the form editing screen (where we added the questions) we can click on “Results” and see some nice charts and graphs. Google provides some insights such as the average score and also identifies frequently missed questions.

screen-shot-2017-01-10-at-9-53-35-am

screen-shot-2017-01-10-at-9-53-47-am

We can also click on the Spreadsheet icon to view the results in a sheet which is better for tracking individuals results.

screen-shot-2017-01-10-at-9-54-05-am

The spreadsheet looks something like this:

screen-shot-2017-01-10-at-9-54-25-am

The spreadsheet is very cool because if you are having students complete a test over and over again until mastery, we can sort the spreadsheet by name and see all of a student’s results together, all time stamped.

Exporting Results

If you did go with the number instead of a name for maximum privacy, we can add names back in fairly quickly by exporting our spreadsheet to Excel.

On your Google spreadsheet go to File>Download as>Microsoft Excel

Screen Shot 2017-01-13 at 9.10.19 AM

Open it up in Microsoft Excel and create a second sheet by clicking on the + beside your current sheet at the bottom of the screen (we can leave it as the default name Sheet1):

Screen Shot 2017-01-13 at 9.12.00 AM

On the new sheet you just created, have your students’ assigned numbers in the first column and the corresponding names in the second column. If you were to do this often, you could already have this list saved in an excel file and simply copy and paste it into the new sheet.

Screen Shot 2017-01-13 at 9.08.57 AM

Still in Excel, return to the original sheet with the quiz responses. Add a new column by right clicking on column B and choosing “Insert”

Screen Shot 2017-01-13 at 9.17.24 AM

 

We can call this column “Real Name”.

Now all we have to do is enter this formula: =VLOOKUP(D2,Sheet1!A:B,2,FALSE) and drag the handle to fill it down. All the formula is doing is looking at column D in this sheet, comparing it with the Value in Sheet1 Column A and replacing it with the Value in Sheet1 Column B.

From here you could sort the results based on name or whatever else you want to do without Google having access to your students’ real names.

Screen Shot 2017-01-13 at 9.08.37 AM

 

Leave a Reply

Your email address will not be published. Required fields are marked *