Create a quick quiz in Excel!

jjones | Guides | 08/05/2007 09:00am
No Comments

1. Multiple Choices

First write your questions (we’re just using three here to demonstrate). Launch Excel 2007 and switch to sheet two. Enter the choices that you want to offer for the first question into Column A, one below each other.

2. Define Name

Repeat the process for the other questions. Now select the answers to question one. Choose Formulas > Define Name > Define Name and enter Question1 as the name. Click OK. Repeat for each answer set.

3. Create Lists

Move back to sheet one and enter your questions into column A. In column B, select the first cell and choose Data > Data Validation. Select List and under source enter =Question1. Repeat for each answer.

4. Mark It

In column C we need to enter a formula to mark the answer. Select the cell and enter =IF(B2=””,””,IF(B2= Sheet2!A2,”Right”,”Wrong”)). B2 is the cell with the person’s answer; here, Sheet2!A2 is where the correct answer is stored.

5. Create Scores

Enter the formula for each question, remembering to change the answer cell and the one containing the correct answer each time. In column D enter =IF(C2=”Right”, 1, 0). Copy this for each row of the quiz.

6. Total Up the Score

Finally, use Autosum to produce a total score and go to the Home tab. Select the Format as Table option and choose an overall layout to make the spreadsheet meet your aesthetic requirements.

Add

to del.icio.us Add to del.icio.us | Digg This Article | Add to StumbleUpon

This entry was posted on Tuesday, May 8th, 2007 at 9:00 am and is filed under Guides. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.