How to Create a Quiz in Excel

Microsoft Excel is an awesome tool to perform a massive number of actions and the ease of creating a quiz is one of them.

In this blog, I bring you another great tutorial to create a self-scoring automated quiz for multiple-choice questions in an excel spreadsheet.

It is effortless to generate a quiz in Microsoft ms excel. Whether you want to create a fun quiz for your friends or a test for your students, using this tutorial you can easily create an automated quiz without any hassle. 😊

I have also designed a quiz template for you which is available to download free of charge from my freebie library. You can use it to create your own personalized quiz.

Whether you find the formulas complicated to apply or short on time to create one, you always have this ready-to-use template at your disposal.

Subscribe To My Newsletters to access free MS Excel Quiz Template

(After subscribing you will receive an email with the URL to my freebie library along with the password.)

Subscribe

* indicates required

How to Create a Quiz in Excel

1. Open an excel spreadsheet.

 

2. Create a multiple choice quiz on the spreadsheet as shown in the screenshot below.

How to Create a Quiz in Excel

 

3. Now on the same sheet create a slot for participants to answer the questions.

How to Create a Quiz in Excel

 

4. Click on the “+” sign to create a new sheet.

How to Create a Quiz in Excel

 

5. On New sheet “Sheet 2” add question numbers in column A and correct answers in column B

How to Create a Quiz in Excel

 

6. In column C, add this formula to cell C2

=IF(Sheet1!K2=Sheet2!B2, “you scored 1″,”you scored 0”)

 

7. Now drag the formula cell by long clicking on the tiny square that will be visible at the bottom right corner of the cell after selecting it. Drag it down till the last row with values. In my file, I will drag it to C6

How to Create a Quiz in Excel

 

8. Let us automate the calculation of the quiz score. In row 7 type Score in cell A7

How to Create a Quiz in Excel

 

9. In C7 apply this formula to count the total score

=COUNTIF(C2:C6, “you scored 1”)

How to Create a Quiz in Excel

 

10. Now try to fill the answers to the answer slot in sheet 1 to make sure formulas are working fine. I answered some questions correctly & some incorrectly to validate.

How to Create a Quiz in Excel

 

11. Here’s how my sheet 2 looks like after populating my answers

How to Create a Quiz in Excel

 

12. Wait! You can not send a quiz file to someone along with the answer sheet attached to it. Anyone can refer to sheet 2 and solve the quiz immediately.

Let’s protect the answers from quiz participants now by hiding them.

On sheet 2 select column B

How to Create a Quiz in Excel

 

13. Hide the column by right-clicking on it and select hide.

How to Create a Quiz in Excel

 

14. After hiding column B, this is how your answer sheet should look like

How to Create a Quiz in Excel

 

15. But what if someone unhides it to access all the answers? Hmmm….

Let’s password protect it now to safeguard the answers.

Right-click on sheet 2 and select protect sheet.

How to Create a Quiz in Excel

 

16. Now type a password in the password dialogue box and click Ok

How to Create a Quiz in Excel

 

17. Retype the password again to confirm and click ok

How to Create a Quiz in Excel

 

Your questionnaire is now password protected and safe to circulate among as many people as you would like. 😊

Congratulations! You’ve just created your first quiz.


More MS-Excel Video Tutorials



Leave a Reply

avatar
  Subscribe  
Notify of