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.)
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.
3. Now on the same sheet create a slot for participants to answer the questions.
4. Click on the “+” sign to create a new sheet.
5. On New sheet “Sheet 2” add question numbers in column A and correct answers in column B
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
8. Let us automate the calculation of the quiz score. In row 7 type Score in cell A7
9. In C7 apply this formula to count the total score
=COUNTIF(C2:C6, “you scored 1”)
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.
11. Here’s how my sheet 2 looks like after populating my answers
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
13. Hide the column by right-clicking on it and select hide.
14. After hiding column B, this is how your answer sheet should look like
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.
16. Now type a password in the password dialogue box and click Ok
17. Retype the password again to confirm and click ok
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