Once you created a form, Google Drive will automatically create a spreadsheet with the same name.

This is where you will find all responses to your questions.

Also, you are going to set up all calculations here.

In the Responses spreadsheet you will see that every question is placed in a separate column. There is also an additional column with date and time when the quiz answers have been submitted.

Select the entire row 2 (your answer keys). I usually freeze it, so I don't accidentally change something in the answers.

Go to View > Freeze rows > Freeze 2 rows (You want to keep the first row with questions frozen too).

Then I insert another column next to the student's name column.

Click anywhere in column B.

Then go to
Insert > Column Right

This is where I will record the correct answers - so I give it this title.

Then I answered the quiz one more time (you see "test" for student's name). This time I intentionally answered some questions wrong, just to check if the formula will work.

Click on the cell C3 (as shown) or, if you have a different structure, the corresponding cell (column: Correct answers, row: test)

### FORMULAS

Now, this is where it can get a little more difficult if you are not fluent with spreadsheets and the formulas. But no worries!

This is what we are trying to achieve in our formula:

- compare each answer with the answer keys;
- if the answer matches the key, it will receive 1 point (or more, if you wish to grant more points for certain questions);
- if the answer does not match the key, it will receive 0 points.

This is what it will look like in the spreadsheet language:

#### =(IF(D3=$D$2,1,0)+IF(E3=$E2,1,0)+IF(F3=$F$2,1,0)+IF(G3=$G2,1,0))

### If this looks confusing, here is what it actually means:

#### =(IF(D3=$D$2,1,0)+IF(E3=$E2,1,0)+IF(F3=$F$2,1,0)+IF(G3=$G2,1,0))

Simply means that we are starting a formula, where the content of the cell will be equal to what is in the parentheses.

#### =(IF(D3=$D$2,1,0)+IF(E3=$E2,1,0)+IF(F3=$F$2,1,0)+IF(G3=$G2,1,0))

This is an "IF" formula.

It says this:

IF cell D3 equals cell D2, then it is given the value 1, if not, then 0

#### IF(D3=$D$2,1,0)

The $ sign makes it an absolute cell reference (to prevent automatic formula adjustments while copying and pasting the formula).

#### =(IF(D3=$D$2,1,0)+IF(E3=$E2,1,0)+IF(F3=$F$2,1,0)+IF(G3=$G2,1,0))

All values, given to each cell, are added for the final grade.

Add as many cell references as you have questions.

Once you've added the calculation formula, hit Enter (Return) to apply the calculations.

The result will show immediately.

You can copy and paste the formula below.

It is good for a quiz with 30 questions. First question starts in column** E.** You can modify it to fit your needs.

=(IF(E3=$E$2,1,0)+IF(F3=$F$2,1,0)+IF(G3=$G$2,1,0)+IF(H3=$H$2,1,0)+IF(I3=$I$2,1,0)+IF(J3=$J$2,1,0)
+IF(K3=$K$2,1,0)+IF(L3=$L$2,1,0)+IF(M3=$M$2,1,0)+IF(N3=$N$2,1,0)+IF(O3=$O$2,1,0)+IF(P3=$P$2,1,0)
+IF(Q3=$Q$2,1,0)+IF(R3=$R$2,1,0)+IF(S3=$S$2,1,0)+IF(T3=$T$2,1,0)+IF(U3=$U$2,1,0)+IF(V3=$V$2,1,0)
+IF(W3=$W$2,1,0)+IF(X3=$X$2,1,0)+IF(Y3=$Y$2,1,0)+IF(Z3=$Z$2,1,0)+IF(AA3=$AA$2,1,0)+IF(AB3=$AB$2,1,0)
+IF(AC3=$AC$2,1,0)+IF(AD3=$AD$2,1,0)+IF(AE3=$AE$2,1,0)+IF(AF3=$AF$2,1,0)+IF(AG3=$AG$2,1,0)+IF(AH3=$AH$2,1,0))

After you have your students complete the quiz:

Copy the cell with formula: Edit > Copy (or Ctrl/Command C)

Click on the cell below (your 1st student).

Edit > Paste (or Ctrl/Command V)

After that, click and drag down the bottom right corner of the pasted cell.

It will calculate all grades at once.

Isn't that nice?!!

You can also adjust the cell formatting for a better view:

I made the row 1 a little taller, and changed the text wrapping to "**wrap**" - so I can read the entire question.

You can also add another column and have the percentage calculated as well.

Add a formula to your t**est row 3**:

#### =C3/4%

Where:

**=** starts a calculation formula

**C3** is the cell with the correct answers

**/4 **out of 4 questions

**%** shows the percentage

Copy the cell formula

Paste and drag down to apply to the rest of the rows.

You can change the background color if you want to...