In this HowTech written tutorial, we’re going to show you how to make a quiz in Excel.
Creating a virtual test or quiz can allow for a great deal of freedom and ease-of-use. Or if you simply wanted to save paper, creating a virtual quiz is a good choice.
To get started, you will need two sheets. One for the actual test and one for the score. To create a new sheet, click the (+) button near the bottom left-hand corner. To rename it, double-click the default text and type the name.
On the Test sheet, you will need designated areas to have the Score sheet check.
After doing so, head over to the Score sheet and type in the answers in one column. In the next column over, type in the following Function: =IF(Test!B15=Score!B2, “Correct”, “Incorrect”).
Test! refers to the sheet that the test is on and Score! Is the score sheet. B15 refers to the cell that the student places the answer in on the Test sheet. B2 refers to the answer on the Score sheet.
When finished, click and drag the bottom right handle of the cell with the Function to encompass all the rows in the Score sheet that have answers.
You will probably notice that it is not referencing the correct cell anymore on the Test sheet. Simply change B15 to the next answer cell (in this case it is B26).
To automatically create a score depending on how many answers are correct, use the following Function: =COUNTIF(C2:C6, “Correct”) * 5 & “ Pts”.
C2:C6 refer to the cells with the =IF function. The output in this case will be “10 Pts” because each Correct is worth 1 point * 5 with the concatenated “ Pts”.
Select the columns with elements on the Score sheet, right-click the letter axis at the top and click Hide (to Unhide, press [Ctrl+A] to select everything, then right-click the letter axis and click Unhide).
Right-click the Score sheet and click Protect Sheet. Uncheck all boxes, then type in a password and retype the password (to Unprotect simply right-click the sheet, click Unprotect, then type in the password).
Home