Creating a Multiple Choice Quiz in Excel

Creating a Multiple Choice Quiz in Excel

Year 7 ICT @ WBGS

Open a new Excel document and enter your first question as shown below…

In cell B4 we are going to add a combo box that will allow the person taking the quiz to choose from a selection of possible answers, much like a multiple choice quiz.

/ Highlight cell B4 then click onto Data – Validation.

In the allow box select List. Then in the source box this is where you type in your possible answers. You must separate each choice with a comma as shown above. You can have as many different possible answers as you like.

Now if you try clicking on cell B4 you see that a combo box appears which allows you to choose your answer.

Adding an IF Statement

Next we need to add an IF statement telling the player of the quiz if they got the answer correct or not.

In cell C4 you need to use the above IF statement. Be very careful of your spelling and make sure you get all the speech marks and commas in the right place.

=IF(B4="Brendan Rogers","Correct","Try again")

Test this and see if it works. Save your spreadsheet!

Now try adding some more questions as below…

Add in 10 questions. Sensible questions only.

If your going to use answers which are numbers you will need to alter your IF statement slightly.

Here you can only select from a number so the IF statement does not use speech marks…

=IF(B8=10,"Correct","Try again")

Keeping a Score

By using a Countif function like you used in the dice simulator you can keep a running score of how many questions the person taking the quiz has got correct.

See if you can add in a score like the example above. Test it and see if it works.

Using Conditional Formatting

Conditional formatting is another feature you would have used in your spreadsheet work.

Highlight the range of cells with your IF statements and click onto Format – Conditional Formatting on the Excel menu.

You then need to setup 2 rules as shown above. Set a different format for the Correct and Try again cell contents.

Test this to see if it works.

Making your Quiz a Finished Product

If you click onto the combo box and press delete to clear the contents of the cell you will see that the answer cell says you have the wrong answer.

To stop this happening you need to alter the IF statement. It needs to read as follows.

=IF(B4="","",IF(B4="Brendan Rogers","Correct","Try again"))

Type this in very carefully!

This will clear the cell if nothing is selected in cell B4.

Do this for all your IF statements. Test it and make sure you save your spreadsheet!

Why not send your quiz to your family and friends? Email it home or put it on your memory stick.

Well done for completing this…! Ask your teacher for a merit.