Create A Simple Right Or Wrong Quiz In Excel

Thu 16th February 2012

Excel reveals part of its fun side with this easy to compile quiz, in which the answers that are entered are revealed as being correct or wrong. As is often the case, however, beneath the fun lies a more practical use.

Compiling such a quiz will reveal how a formula can be adapted to create a desired action within a specific cell.

In an early episode of The Simpsons, our favourite nuclear family, accompanied by Patty and Selma, are watching a television quiz show while devouring TV dinners. A contestant on the show is asked to name the third colour of the Italian flag after red and white. Amid many shouted guesses, Homer offers red, then white then black and finally green. When green is revealed as the correct answer, a delighted Homer gives himself a round of applause with a cry of "I was right". How nice it would be that we could submit four answers to each question on Who Wants to be a Millionaire?

This set me thinking about a rather neat quiz-type trick in Excel, in which one column contains questions, a second is left blank for the user to type in his or her answers, and a third reveals whether those answers are right or wrong. I dare say this arrangement would meet with Homer's approval, as he could keep typing in answers until he came to the correct one.

Here is how I set up the sheet, complete with column headers.

Column B - No.
This column lists the number of each question.

Column C: Question
The questions are typed into this column.

Column D: Answer
The user types his or her answers into this column, so it remains blank.

Column E: Reveal
The answers given are revealed as either correct or wrong in this column.

To begin with, I entered the question numbers in column B, for the purposes of this project, these only went up to 2. For the first question, I stuck with the one from The Simpsons episode, which I typed into cell C3.

The colours of the Italian flag are red, white and what?

After I'd typed this, I double-clicked on the right edge of the C column name at the top of the screen. This expanded the width of the column just enough to accommodate the full text of the question. The next stage was to tell Excel the correct answer which, as we know, is Green. I set aside the cell D3 for the user to type the answer into, and in the cell E3, I typed the following formula:

=IF(D3="", "", IF(D3="Green", "CORRECT", "WRONG"))

With the question set, I deliberately typed Homer's incorrect answers into cell D3. Each time I pressed Enter, the word WRONG appeared in cell E3. Finally I typed 'Green', and this time the word CORRECT appeared. And that was it, I had set up my first question without much trouble at all.

I continued the Simpsons theme with my second question.

What is Chief Wiggum's first name?

Simpsons buffs will know that the answer this time is Clancy. I typed the question into cell C4, left D4 blank for the answer, and prepared the following formula for cell E4:

=IF(D4="", "", IF(D4="Clancy", "CORRECT", "WRONG"))

If you compare these two formulas, you will see that the only changes I made were to allocate the new cell by changing D3 to D4 (twice), and replacing the answer 'Green' with 'Clancy'. The easiest way to create formulas for additional questions, therefore, is to copy the original and make those minor adjustments.

A word of caution, though; tinkering with formulas in Excel can lead to all kinds of unexpected things happening, such as coloured lines appearing and cell borders becoming animated . A simple way to update your formula safely is to copy the original and paste it into a Word document. From here you can make alterations and then copy the updated formula and paste it into your formula bar.

If you compare these two formulas, and make a note of where changes were made, you should be able to continue adding questions to the quiz by following the same pattern. But before you go any further, let us return to the first question to alter another part of the formula.

Instead of having a column of reveal cells that simply indicate whether an answer is right or wrong, you could customise it to suit the questions you are asking. If you look at the formulas above, you can see where the reveal options are. Again it is simply a matter of deleting these and entering new text. In this Simpsons-related quiz, it would add to the fun if you deleted the words CORRECT and WRONG, and replaced them with the more appropriate reveals in this formula.

=IF(D3="", "", IF(D3="Green", "Woo-hoo!", "D'oh!"))

So now if you enter Homer's incorrect answers, you will get an appropriate 'D'oh!' for each one, and a hearty 'Woo-hoo!' will appear when the correct answer is entered. If you set up this formula for your first question, these reveals will remain every time you copy and paste a formula for each subsequent question.

Once you are familiar with the various elements of the formula, you can adapt the responses to suit almost any situation. For example, a football quiz could take the form of a penalty shoot-out, with the word GOAL appearing for a correct answer, and MISS for a wrong one. I did try creating a formula with a basic tick and cross for the answers, but the tick appeared as the oblongs of a Wingdings character. Maybe you can find a way around this.

While setting a quiz in this manner is fun, it also serve a more practical purpose, in that it demonstrates how a formula can be tailored to create a desired action within a specific cell. This versatility is one of the reasons that Microsoft Excel such a widely used application within the modern workplace. So whether the training you receive is on-site, consultancy or one-to-one, you know that in mastering Excel your skills will be in demand for years to come. It's a no-brainer really, isn't it?

Correct.

Author is a freelance copywriter. For more information on excel training uk, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2045-create-simple-right-or-wrong-quiz-in-excel.html