How to create an automated quiz in Microsoft Excel

If you are a teacher, you have to create quizzes for students on a regular basis. Many free quiz maker software are also available but it can be risky to install them on your PC if they are not downloaded from official or trusted websites. Do you know, you can create automated quizzes without installing third-party software on your PC? In its article, we will show you the procedure to create an automated quiz in Microsoft Excel.

How to create a Quiz in Excel

Here, we will create all multiple choice questions in sheet 1 and make an answer sheet in sheet 2. To automate the quiz, we will write formulae in sheet 2. Let’s see the process.

1] Launch Excel and write multiple choice questions in sheet 1. Here, we have merged some cells using the “Merge and Center” option. See the below screenshot.

2] Now create a new sheet by clicking the “Plus” icon adjacent to sheet 1.

3] You have to write the correct answers in sheet 2.

4] Now, we will automate the quiz by writing the formulae in sheet 2. Write the following formula in column C of sheet 2.

=IF(Sheet1!J2=Sheet2!B2, "you scored 0","you scored 1")

 

Please view the cell addresses carefully in the above formula, where J2 indicates the cell of sheet 1 in which answer to the question number 1 is to be written. B2 indicates the cell of sheet 2 in which we have written the correct answer to question 1. We are getting the value “you scored 0” by default because initially, no student has attempted the test. You have to write the correct cell address otherwise you will get an error.

5] Now, drag the selected cell to the last cell. This will copy and paste the formula to all the empty cells.

6] Now, we have to enter a formula to calculate the score. For this, write the following formula to cell C6. You can select any cell to calculate the score.

=COUNTIF(C2:C5, "you scored 1")

In the above formula, C2:C5 indicates the range of the number of questions. We have a total of four questions, therefore, we have selected this range.

The automated quiz is ready. But you have to protect sheet 2 so that no student can edit the values or see the correct answers. For this, we will hide the column of correct answers and protect the sheet with a password. To hide column B, select it, right-click on it and then select “Hide.”

To add password protection to sheet 2, right-click on it, select “Protect sheet,” create a password and click OK.

This is how you can create an automated quiz in MS Excel by using formulae.

Let us know if you have any questions.

Read: What is the maximum number of columns & rows in Excel.