Creating Interactive Quizzes in Excel
J.A. (Jim) Connell University of Montevallo Michael E. Stephens College of Business Station 6551 Montevallo, AL 35115 Elizabeth Mulig USF St. Petersburg 140 Seventh Avenue South St. Petersburg Florida 33701-5016 727-553-4154 firstname.lastname@example.org
Introduction Have you ever received a spreadsheet file that asked you questions and then magically told you whether your answers were correct? They can be more than e-mail pastimes. Once you understand how those spreadsheet files list questions and check your answers, you can create spreadsheet files that ask your students accounting questions. All it takes is an understanding of a few simple functions within the spreadsheet, and we’re about to reveal those secrets. They’re a lot easier to create than most people think and it only takes one simple formula to produce a scoring system that makes your quiz interactive. This means that your answers will be included in the spreadsheet, but it’s easy to hide the answers and password protect the spreadsheet so your students have to work to find the correct answer. To illustrate how it works, we’ve created an interactive quiz based for a principles-level accounting class. Once you understand the function, you can adapt this to your own needs by changing the headings and questions. We used Microsoft Excel 2000 here, but the concepts work with earlier versions of Excel and other spreadsheets. Background & Objectives This document explains the creation of a self-scoring, interactive quiz using Excel. The reader should be able to create an interactive quiz after reading and reviewing this document. Practice with sample questions is an effective way for students to study for upcoming exams. Interactive quizzes, as described here, are one way to provide a set of sample questions to students before the actual test. Quizzes like this offer practice questions to students and (hopefully) motivate them to spend more time working homework problems and answering questions. By using Excel, students do not need an active internet connection to study students are entertained enough to practice answering potential test questions students gain familiarity with Excel as they try to find where the correct answers are hidden in the worksheet. The basic process is to put the questions in one worksheet (Test #1 Questions) along with marked cells where students enter their answers. A second worksheet (Score) contains formulas that compare the student’s answers to the correct answers in Score. The column containing the correct answers is hidden and Score is password-protected to prevent student access. Step 1: Format two worksheets Open Microsoft Excel to a blank workbook file. We need two of the worksheets within this workbook to create the interactive quiz, so the first step is to delete one of the three worksheets that normally appear when a new workbook file is created. At the bottom of the screen, the default three worksheets are usually named Sheet1, Sheet2, and Sheet3. This quiz only needs two worksheets, so the first job is to delete Sheet3. Right-click on the Sheet3’s sheet tab at the bottom of the screen, select Delete, and then select OK to confirm the deletion. Double-click on Sheet1’s sheet tab and its name will change to reverse print (white text on a black background). Enter the name Score over the old name, then click on any of the cells or hit enter. Format this worksheet to display the quiz scores; one section for the overall grade and one section for individual scores). Click at the top of Column A so that the whole column is highlighted. From the menu at the top of the screen, select Format, Column, Width, then set the width of column A to 18, then center the text. Follow the same steps to set the width of Column B to 11 spaces, centered.
1 of 4
11/29/2012 11:36 PM