How to Create a Quiz in Excel. Excel is a great program for creating spreadsheets and tracking information, but it is also useful for creating quizzes. Making question and answer quizzes is a good way to improve on your Excel skills while having a bit of fun.
Step 1
Open a blank spreadsheet in Excel. Use column A for the question, column B for the answer and column C to grade the answers.
Video of the Day
Step 2
Type your first question into cell A1. For the purposes of this demonstration the question is, "If you mix red and blue together, what is the resulting color?"
Step 3
Scroll or arrow over to cell C1 and type the following function: =IF(B1="", "", IF(B1="answer", "Right", "Wrong")). Replace "answer" with the correct answer. Using the example, "If you mix red and blue together, what is the resulting color?" you would replace "answer" with "purple" and as long as the person taking the quiz typed "purple" in the corresponding B1 cell, the answer would show "Right". If someone gives a wrong answer, "Wrong" shows up and if they put no answer, cell C1 remains blank.
Step 4
Think of questions and answers to fill the quiz. Remember column A is for inputting questions, column B should be left blank with enough space to type answers and column C is where you input the formula.
Step 5
Continue inputting questions into column A by typing your questions into subsequent cells.
Step 6
Skip over to the C column and begin typing in the formula into the cells in the C column. Remember to change "B1" from the original formula to the B cell number that corresponds to the question. For a question in cell A2 you will want the formula to read =IF(B2="", "", IF(B2="answer", "Right", "Wrong")), again replacing "answer" with the correct answer.
Step 7
Decide if you want to quiz to score the participant's answers. If so, simply input the following into the corresponding D cells: =IF(C1="Right", 1, 0). Remember to update the cell numbers to correspond with each question and answer.
Step 8
Type the following formula at the bottom of the quiz if you have decided to have Excel calculate the score: =sum(D1:D10). This is the formula for a 10 question quiz--adjust accordingly. You can either write in the cell numbers, or after you have typed the opening parenthesis you can use your mouse pointer to click and drag to select the range of cells you want added together. If you chose to select the cells this way they should be highlighted with a blue box.
Step 9
Hide the answers before you send the quiz out. Select column C and choose "Format> Cells>Protection" and check the "Hidden" selection box. Select "OK" to close the window and apply the setting. Now you can send the quiz out knowing the answers are hidden.
Video of the Day