Create a Placement Test with Google Forms & Sheets

Students who want to study a language at a language academy, have to do a placement test, which confirms their current level in that language and then tells the tester in which level they should be placed.

Here we’re going to see how a combination of Google Forms and Sheets, the functions VLOOKUP and ARRAYFORMULA and conditional formatting, can produce a simple but effective placement test.

---

SETTING UP THE FORM

First, we create the test using Forms. If you’re new to using Forms, then see my posts on how to use Google Forms.

In the test, I want the students to record some contact information and then below will be the test questions.

Here we have the fields to record their email address, name and telephone number, all of which are obligatory.

To set up the email address field so that it checks to see it’s a valid one, we go to the settings cog (on the right here).

Then tick “Collect email address”. Then click “Save”. This will add an email address field with data validation, which will check to see if an email has been entered. Note, that this always puts this field at the top of the form. Personally, I’d prefer to be able to move this, but at present that’s not possible.

Then we add the multiple-choice questions in our test.

I want the test to automatically correct this, so  click on the settings cog and this time select “Quizzes”. Then select the “Make this a quiz” option. Under “Release mark” choose “Later, after manual review”, so that the student doesn’t have the option of seeing their marks as soon as they’ve finished the test. I also untick all the options under “Respondent can see”.

Back in the questions part, we now need to tell the form,which questions are correct. Click on a question and then “Answer Key”.

Then click the correct answer. This will add a green tick next to it. Also, change the points to 1.

Repeat this for all the questions. Now we need to link the form to a Sheet. Under “Responses”, click the green Sheet icon.

We want to create a new spreadsheet, so leave the option selected and click “Create”.

---

SETTING UP THE SHEET

This will open the Sheet and you will see the different fields from the Form on the sheet.

I usually format this a little and sometimes move the columns around to suit my needs, for example, like this:

Add a new page and let’s call it “Levels”. This is where we’re going to add the levels which correspond with the result from the test. In this example, we have 3 possible levels A1, A2, B1. In columns A and B and add the ranges for each of those levels. So, for example, if someone gets 6/10 in the test, the level they will go in is A2.

I’ve added a row (row 3) with just zeros, and nothing in the level column. This is important as we will see later, so that if there is nothing in the results column on the form responses sheet, nothing will be added to the levels column.

Now, let’s see if it works. The first time, I fill it out with the correct answers just to check everything is working ok. It also allows us to check future responses against this answer key, as we’ll see below.

As we can see the first response have been stored in the sheet fine.

Now we want the sheet to automatically add the appropriate level for the result. To do this we use two functions, VLOOKUP and ARRAYFORMULA. The VLOOKUP function, looks at the results in column B from B2 to the end of column B (it’s open-ended and will automatically continue as new rows are added), then looks up that result in the table on the Levels page in range A3:C6, and gets the level that’s stored in the 3rd column.

For more information on VLOOKUP see this post.

We add this to the ARRAYFORMULA function, which will automatically add this formula to all the rows below, so there’s no need to add the formulas for every row.

When we submit another test, as you can see the responses are added to the row below and the level is automatically added.

Note, that no level is added where there is no responses, this is because of the zeros line we added earlier in the levels table.

---

HIGHLIGHTING WHICH ANSWERS WERE CORRECT OR INCORRECT

Sometimes we also want to see which answers the students got right and wrong. We could do this back in the form, but usually we’re only working with the sheet, so it can be useful to highlight here.

Select the answers from the second response (row 3 (e.g. G3:P3).

Right-click and select Conditional formatting.

This will open the conditional format rules side bar. See my post on Conditional Formatting for more info.

First, let’s apply this formatting to all the columns from G to P. Currently we only have G3:P· selected.

Change the range to an open-ended one, so that the range will always go down to the last row on the sheet. This means even if more rows are added, all responses received will be formatted.

Click on the drop-down menu “Cell is not empty”. Then select “Custom formula is”.

Here, add the formula “=G3=G$2”. This will check if the answer in G3 is the same as G2 and if it is, it will fill the cell green. This will also check all the other cells in the range and will update the cell references accordingly. So, it will check H3 is the same as H2 and so on. The use of the dollar sign means in rows below, the answer row reference won’t change, e.g. it will check that G10 is the same as G2 and so on.

Now we need to add an extra rule to show the incorrect answers in red. Click on “add new rule”.

Here, we want to check if G3 is different from G2 and if so to fill the cell in red. So, we use the not equal symbols: <>

The problem with this is that as we are using an open-ended range, this will fill all the cells on the sheet in red that aren’t the same as the answer key.

So, we need to change the formula so that it checks to see if the answer is different AND if the row isn’t blank. We use the following AND function, which checks both conditions.

Now, it only highlights the rows with answers in them.

---

Here’s the link to make a copy of the Sheet and Form.