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

mulig@stpt.usf.edu

 

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,

 

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.

 

In cell A5, enter Overall Score: and right-align the text. In cell A10, enter Individual Questions and center the text. Beginning in cell A11, enter the number 1 and continue numbering down the column for as many questions as desired.

 

Follow the same procedures to rename Sheet2 to Questions and format Column A to a width of 5 with centered text and Column B to a width of 60 with left-justified text. In cell B1, enter the heading ďQuestions

 

Step 2: Enter sample questions in Questions.

One problem with using Excel is that it does not have a word wrap feature. Long strings of text have to be entered one line at a time, with each line fit to the column width. For our example, enter the following text in the indicated cells in the Questions worksheet,

 

Cell

Text

A3

1.

B3

The present value of $2,500 to be received in four years when interest

B4

is 12 percent compounded quarterly is computed by discounting at

B5

†††† A)12 percent for 4 periods

B6

†††† B)3 percent for 16 periods

B7

†††† C)4 percent for 12 periods

B7

†††† D)6 percent for 8 periods

 

The correct answer is B, so please format the contents of cell B5 to bold text, to keep track of the correct answers while working.

 

Step 3: Format student answer cells in Questions so they stand out

This step has two purposes. First, students have to put their answers into designated cells so the quiz can grade itself. Second, it makes the worksheet more attractive and easier to read.

 

Highlight the first answer cell (C3, in this example), select Format/Cell from the toolbar at the top of the page, and select the color or border options desired. When working with multiple questions, the format painter is the quickest way to copy the chosen format to all answer cells.

 

Step 4: Enter correct answers in Score for comparison

Now we need to build an answer key to use when grading the quizzes. The first step is to enter the correct answers in the answer cells on the Questions worksheet so we can work with them. These questions need to be moved to the Score worksheet, so that we can hide and password protect them.

 

For this example, we will hide the correct answers in column Z of Score. The steps to accomplish this are,

1)       click at the top of column C in Questions to highlight the entire column

2)       right-click and select copy

3)       move to the Score worksheet

4)       click at the top of column Z in Score to highlight the entire column

5)       right-click and select paste

 

Step 5: Remove correct answers from the Questions worksheet

As you may have already guessed, the Score worksheet is both the grade report and the grading key for this quiz. Now itís time to clean up some of our work on the Questions worksheet. Sometimes the last step in writing an in-class test is to remove the marks identifying the correct answers. We need to do the same thing to our spreadsheet.

 

Go back to the Questions worksheet, reformat the correct answers in to the standard text format instead of the bold text they were in and delete all of the correct answers from the answer cells (just C3 in our one-question example).

 

Step 6: Enter formula to grade individual questions.

Now its time to enter the one formula that will grade all the questions. Actually, it takes one formula for each question, but after the first formula is entered, itís just a matter of cutting and pasting it as many times as necessary. In Step 6, we put the title, enter Individual Questions in cell A10 and the number 1 in cell A11. We are about to grade question number 1 and put the results in cell B10.

 

In cell B11, enter, =IF(Questions!C3=Score!Z3,"Correct","Try again")

 

This is an Excel function of the form,

 

IF(logical_test,value_if_true,value_if_false)

 

Translated in to English, it tells the computer to compare the contents of cell C3 of the Questions worksheet with cell Z3 of the Score Worksheet. If the two cells are identical, cell B11 will display the word Correct. If the two cells are not identical, cell B11 will display the phrase Try again.

 

This function is not case-specific, so mixing upper and lower case letters will not cause incorrect results.

 

When you are working with multiple questions and cutting and pasting this formula to multiple cells, you may need to correct the formula row and sheet references (C# & Z#) so they refer to the appropriate answer cells and answer key cells for each question.

 

Step 7: Enter formula to calculate overall score in B5 of Score.

Sooner or later, you will want to use more than one question. When that day comes, the students taking the quiz will want to know how many questions were right. Since we only have one formula right now, we can start with the following function,

 

In cell B5, enter, =COUNTIF(B11:B11,"Correct")

 

This cell contains an Excel function of the form,

 

COUNTIF(range,criteria)

 

The COUNTIF function counts the number of cells within a range that meet a given condition. The range is defined by the B11:B11 term and the condition is whether the cell is displaying the word Correct. Remember, the formula in cell B11 compares the studentís answer to the answer key cell (cell Z3 of Score) and is displays Correct if the answers match.

 

In other words, this formula simply counts the number of individual answers marked Correct.

 

Later, when you want to use more than one question, this formula will need two minor changes.

1)       Modify the range so that it includes all of the answer cells. For example, if your answer cells are spread out from B11 down to B53, edit the formula to include the term B11:B53 instead of B11:B11.

2)       If you want to provide an overall percentage score; add the term /10 to the end of the function, if, for example, if you have 10 questions, then format the cell to percentage format. The number you add after the division symbol is the number of questions you want the raw score to be divided by. With this addition, the formula will count the number of correct answers and divides by 10

 

Step 8: Hide the correct answers and protect the Score worksheet.

We like to hide the answer key cells, just to make the students work for the correct answer. Still on the Score worksheet,

1)       Right-click at the top of column Z to highlight the column

2)       Go to the toolbar at the top of the screen and click on Format/Column/Hide

3)       Column Z should now disappear.

 

Even though column Z is hidden, anyone can notice that the columns are now labeled X, Y, AA, and realize something is wrong. To keep column Z hidden, you need to protect the Score worksheet.

 

In the toolbar at the top of the screen, select Tools/Protection/Protect/Sheet. Create a password and re-enter it when prompted to confirm it. Make sure to write this password down because there is no simple way to unprotect this worksheet if you forget the password.

 

Conclusion

This interactive quiz is now ready to save and distribute to students. Just tell them to go to the Questions worksheet and enter the letter of the correct answer in the marked answer cell in column C, then they can go back to the Score worksheet to check their grades.

 

The key to the interactive feature is using the IF function to compare cells on different worksheets. Now that you know how to apply this to multiple choice questions, you can adapt the technique to use true/false and matching questions, or perhaps youíll create something we havenít even thought of yet.