Using forms in Google docs lets anyone create forms quickly and share those forms via email, embed them into a webpage or blog. If you are a teacher, you can create formulas that allow you to have these forms graded in minutes. The formula part is a bit challenging, so I wrote this article to talk about how I recently created a final for one of my classes.

Create a new Form in Google Docs

To Create a form, go to the Create New menu and choose form. Google will open up a new window with the form. You can type in a form name and description as well as start typing your first form.

Type in your questions, help text and question types

There are different types of questions you can choose from. It’s best if you try to make a question that has each of these elements to familiarize yourself with them.

Make some quiz questions required

You can also make some questions required. For example, my first question is the student’s name. I always make this a required question to make sure the students answer it.

Choose a test quiz type

I’m going ask 5 questions of different types on this quiz. I’ll also add a section for the student’s name. Google docs will automatically create a timestampthat lets you know the date and time the students filled out the test. To add each questions, just go to the top of the screen and choose Add Item, choose the question type and start typing your questions and options. This part is pretty self-explanatory and shouldn’t take you very long.

Here’s what my test looks like when it’s done. When you’re ready to go, click on the SAVE button at the top right of the page.

Choose how viewers will take the quiz

Once you’ve finished the test, you have three options to give people access to your quiz. You can click on the Email this form at the top of the screen, then fill out a list of recipients in the box provided, you can click on More Actions, then chooseEmbed from the pop-up. You can then copy this code and put in on a webpage or a facebook page. Finally, you can click on or copy the list at the bottom of the test and send someone the URL where they can take the test online.

Take the test yourself to create a KEY of answers

In order to make the test grade itself, you’ll have to create a KEY of answers. Click on the link at the bottom of the test and fill out the test yourself. Hit submit when you’re done

Take the test a second time to create a sample perfect student

The second time you take the test, you’ll pretend to be a student, answer all of the questions correctly. This will help you check to make sure your formulas are correct when you create them. The last question is an open ended question and will have to be treated differently than the others.

Check out the test results

You can go back into Google Docs and see your form in the list of documents. Click on it and you’ll see something like the spreadsheet above, it will contain all of the entries that have been submitted. A really great feature of Google Docs is the ability to see the information in graphic form. You can see that by going to the FORM menu and choosing Show Summary of Responses.

Create Additional calculated Colums

We’ll add a few more columns to finish up. We’ll need a column for points someone would get since it’s open ended. That will allow me to give the student points if they get that question partially right. I’ll call this column Open Ended Points. Then, I’ll add another colum to calculate the Correct Points from the other questions. I’ll call that Correct Points. Finally, I’ll add a final colum for the grade. I’m also going to fill out some sample points in the open ended points colum to test my calculations.

Create the formula to calculate the correct points

this is the hard part. To calculate wether a questions was filled in correctly, I need to award a point to a student if their answer matches the answer in the key. We’ll need to use a spreadsheet if statement. The IF statement works like this IF(CELLID2=CELLID1, TRUEVALUE, FALSEVALUE). So, if the answer on the current cell matches the answer on the key, then the spreasheet will give the TRUEVALUE, otherwise it will give the falsevalue.

So, for our first question the formula would look like this: =IF(C3=$C$2,1,0). The = sign at the beginning of a formula tells the spreadsheet software that this is a special cell that needs to be calculated. Notice something peculiar about our cell references. The first cell reference C3 is a normal cell reference. The second cell reference is a bit different $C$2. This is an absolute cell reference.

The cool thing about formulas in spreadsheets is that they can be copied and pasted into other cells. When you have cell references in them, the spreadsheet application will try to make the fomulas automatically adjust to the relative position of the cells. If we had another student fill out this form and copied the formula from this cell to the one below, The spreadsheet would attempt to check to see if the answers would match not the key, but the cell right above the current cell. Since we don’t want this to happen, the second cell reference is absoute. When the formula is pasted, the answers will always be checked against the first test answers (the key).

To calculate the Correct Points, we need to add the value of each correct answer. Here’s what that formula would look like:

=(IF(C3=$C$2,1,0)+IF(D3=$D$2,1,0)+IF(E3=$E$2,1,0)+IF(F3=$F$2,1,0))

Here’s a couple of caveats about doing it this way. For a checkbox question, the student must click all of the correct checkboxes in order for the question to count. There is no points for partially correct answers. Also, for questions that are fill in the blank like the question about the IAB, the answer must match exactly. Some students, might have a problem remembering how to spell Bureau or make a spelling mistake. Those won’t count. Of course, you can go through this spreadsheet and when you’re going through and analyzing the Open ended questions, you can retype their answers to make sure they match and they get the points, but I would mention that they must get everything perfect or it might not count.

The formula to calculate the final grade will take all of the points from the Correct Points column and add them to the open ended points. That formula looks like this:

=ROUND((H4+I4)/($H$2+COUNTA($C$2:$F$2))%,0)

This formula adds up the previous two colums, then compares that to column $H$2 (the points I am awarding for open ended points in this quiz, plus the number of columns between $C$2:$F$2), which is a fancy way of calculating the total amounts of points on this quiz. Normally, this calculation woud give you a number between 0 and 1, but if we add the % at the end, it creates a percentage result which converts our normal results to a number between 0-100 %. Finally, we wrap this around a ROUND function. The round function works like this ROUND(VALUE, ROUNDTODECIMALS). By giving it a 0 value in the ROUND to decimals, we make this result round to whole numbers.

Take the test again a couple of times

To test things out a bit more, we’ll take the test a couple more times and I’ll show you how to easily and automatically get the results for the new people. To take it again, go to the FORM menu on the spreadsheet and choose GO TO LIVE FORM. If your students are taking the test, you can see their results in real time as soon as they hit the submit button.

Duplicate the formulas

Once your students are done taking the test, check their open ended questions and award them open ended points. Then, select the two calculated cells (I3 and J3 in this case) and click on the bottom right of the two cells (there will be a small blue rectangle there) and drag them down to the end of the list of students who have completed the test. Their grades will calculate automatically.

It’s always a good idea to manually verify a couple of test grades with a calculator to make sure your formulas are correct. If you want, you can try taking this test. I’ve shared it so you can view the spreadsheet as well.

I used this formulas to give my students a final exam and it worked out great. They could obviously look for the answers online so I gave them a limited time to work on the final. I had my grades done immediately after they took the test and I didn’t have to spend hours grading them.