Creating a quiz in Excel

You can create a quiz in Excel. It can track correct and wrong answers, and keep a running total. You can make up your own list of questions but try to keep the answers simple.  

Structure the workbook

You need to structure the workbook to have the Questions on one sheet and answers on another. For this example we will assume there are 10 questions. 

• Rename Sheet 1 to Quiz and Sheet 2 to Answers (Double click the tab)
• In the Quiz sheet B1, type Number of Questions
• In B2, type Your Score
• In A4 type Question,
  B4 type Answer and
  C4 type Result
• In A5, type the first question
• In A6 type the second question and
  a question in each cell below that (A7 to A14)
• In the Answers sheet starting in cell A5, type the answer to the first question
• Continue with the answers to A6 to A14 matching the answers with the questions
• On the Quiz sheet, adjust the width of column A so you can read the questions

Add the formulas

You need to insert formulas to check answers and keep score.

• In cell C5, type the formula:
  =IF(B5="","",IF(B5=Answers!A5,"Correct","Incorrect"))
  This checks the answer in  B5 to see if it matches the contents of cell A5 in the Answers sheet and displays Correct or Incorrect
• Copy this formula down column C so it appears opposite each question
• In C1, (opposite Number of Questions) type the formula:
  =COUNTA(A:A)-1
  This will return the number of questions in your quiz
• In C2, (opposite Your Score) type the formula;
  =COUNTIF(C5:C14,"=Correct")
  This counts the Word Correct

Format the sheet

The easiest way to format it is as a Table

• Select all the cells from A4:C14 (the last question)
• From the Home tab, in the Styles group, click the Format as Table command
• Choose a format and click OK
• With the Table still selected, from the Table Tools - Design tab, in the Tools group, choose Convert to Range (this removes the unwanted “Table” features)
• Select the cells B5:B14 (the answers)
• Right mouse click and choose Format Cells
• Select the Protection tab, deselect Locked and click OK (this will unlock the cells so that data can be entered in them when the sheet is protected)

Hide and Protect

You now need to hide and protect your workbook.

• Right mouse click the Answers sheet and choose Hide (the whole sheet will be hidden)
• With the Quiz sheet selected, from the Review tab, in the Changes group, choose Protect Sheet and the dialog box appears
• Under Allow all users… make sure only
  Select Unlocked Cells is selected
• Type a password to protect the sheet and click OK
• Re-enter the password and click OK
  Users can now only select cells in column B and
  they won't see the formulas that indicate where
  the answers are 
• From the Review tab, in the Changes group,
  choose Protect Workbook
• Select Structure and type a password (this will protect the workbook so the 'Answers' sheet itself can't be unhidden)