Gallery: Design a multiple-choice quiz in Excel

Launch Excel and a blank workbook will be created automatically. We’ll start by setting up the names for the worksheets. Right-click on the tab called Sheet1 at the bottom of the screen and choose Rename from the pop-up menu. Change the name to ‘Welcome’ (without the quotes). Now right-click on the Sheet2 tab, pick Rename again and this time call it Questions. Finally, right-click on Sheet3 and rename it Answers. Open the File menu, choose Save and save the workbook with a suitable name, such as Quiz.

Click the Questions tab to select that worksheet. Type the following headings into the first row: Question in cell A1, Answer in cell B1, Correct? in cell C1, and Score in cell D1. Click on the number 1 in the numerical row headings down the left-hand side of the sheet to select all the cells in the first row: now hold down Ctrl (Control) and tap B to have Excel style them all in bold formatting. To make the headings stand out further, experiment with the various formatting options – try different typeface sizes and colours but try to stick to similar fonts (Arial Black with Arial, for example).

Right-click the column heading A and choose Column Width from the pop-up menu. Type in 45 and click on OK. If more space is needed, change the figure to a higher number. Repeat in turn for columns B, C and D, allocating less space to columns C and D. Now click on cell A2 and either select Freeze Panes from the Window menu (Excel 2003 or earlier) or switch to the View ribbon and click on Freeze Panes followed by Freeze Top Row (Excel 2007). This ensures the quiz headings always remain visible on screen, wherever the player may be in the spreadsheet.

Type the first question into cell A2. It’s likely that the question won’t fit the available space, so right-click on column A again but this time select Format Cells. Switch to the Alignment tab and tick the box marked ‘Wrap text’ before clicking on OK. You’ll see the question now takes up as many lines as it needs in the cell, which is expanded to fit. Try to keep each question to no more than two lines, as this will ensure the quiz looks neat and tidy.

Cell B2 is where the person playing your quiz inputs their answer. To prevent possible confusion over spelling, it’s best to provide them with a selection of answers to choose from in the form of a dropdown menu. To do this, first click on the Answers tab to switch to that worksheet. Now type four possible answers into cells A1, A2, A3 and A4. Using the mouse, click and hold down the left mouse button to select all four of these cells and then type Question1 into the Name Box that sits just above columns A and B. Press Enter.

Click to switch back to Questions and select B2. Create the menu with your answers by opening the Data menu or ribbon in Office 2007 and clicking Validation. Switch to the Settings tab, click the arrow next to ‘Any value’ under the ‘Allow:’ heading and choose List. In the Source box type =Question1. Click the Input Message tab and type ‘Click the down arrow to make your selection’ into the Input message box. Click OK and then the arrow next to cell B2 to check your answers appear.

Cell C2 is used to confirm if the answer is correct. Switch to the Answers tab to check which cell contains the right answer. Type the following formula into cell C2: =IF(B2=“”, “”, IF(B2=Answers!A2, “Right”, “Wrong”)). Make sure there are no spaces between each pair of quotation marks found after IF(B2=. Replace A2 with the cell containing the correct answer. Pick an answer from the menu in cell B2 to verify cell C2 is set up. We have checked this formula so if it doesn’t work, try retyping it. We have put our completed spreadsheet up on our website to help you out: download it from www.computeractive.co.uk/2200164.

Cell D2 is used to keep score. The formula to type is: =IF(C2=”Right”, 1, 0). You can now enter another question into cell A3, then switch back to the Answers tab. Type the answers into cells B1 and B2 and (you can have as few or as many answers to choose from as you like). Select them all and type Question2 into the Name box before pressing Enter. Repeat step six but select cell B3 and type =Question2 into the Source box when prompted.

Right-click cell C2 and choose Copy. Now right-click on cell C3 and then on Paste – you’ll notice the formula has been updated automatically to look at cell B3, although it still looks to column A on the Answers tab for the correct answer. You’ll need to update the B3=Answers!A3 reference to point towards the correct answer on the Answers tab (for example, B3=Answers!B4). Now repeat the process to copy the contents of cell D2 to cell D3. The formula will automatically update again to look at cell C3 but this time there’s no need to alter it.

Add as many questions and answers as you like. You can even include a photo or sound clip (in the WAV format) with questions if you want. It’s a good idea to keep the file sizes small, though, so pick short sound clips or JPEG images, which are well compressed. You need to insert a row above the question to which the clip refers. To do this, select the cell containing the question. In Excel 2007 click on the Insert button on the Home ribbon and select Insert Sheet Rows. In earlier versions of Excel, open the Insert menu and choose Rows.

Excel 2007 users should switch to the Insert ribbon. Open the Insert menu in Excel 2003 or earlier. To insert a picture, choose Picture or Picture followed by From File. To insert a sound file, choose Object, then switch to the Create From File tab. Click on Browse to pick your file. Drag the item into place on the spreadsheet. Sound files appear as small icons (players will need to double-click it to hear it). Pictures must be resized to fit. You then need to expand the size of the new row so your question sits below the picture or sound clip.

Once all the questions are in place, let the player see how their score is progressing. Select the cell in column D immediately below the last question and type: =SUM(D2:D11), where D11 is the cell directly above the one you’re editing. It is also possible to give feedback based on how many points have been scored. The following formula is based on a quiz of 10 questions and assumes the score is kept in cell D12. Type it into the cell below the current score: =IF(D12<4, “Poor”, IF(D12<7, “Okay”, IF(D12<10, “Very good”, “Perfect!”))).

You might want to prevent players from stealing a peek at the answers. Start by switching to the Answers tab. In Excel 2003 or earlier, open the Format menu and choose Sheet followed by Hide; Excel 2007 users should right-click the Answers tab and choose Hide. On the Questions tab, right-click column B and choose Format Cells. Select the Protection tab and untick the Locked box. Click on OK. Now switch to the Review ribbon and click on Protect Sheet (Excel 2007) or open the Tools menu and select Protection followed by Protect Sheet (Excel 2003 or earlier).

Enter a password in order that only you can edit the quiz and, if applicable, make sure only ‘Select unlocked cells’ is ticked before you click on OK. Re-enter the password and click on OK again. Notice now that only the answers column can be clicked. To complete the job, choose Protect Workbook from the same menu or ribbon as before – by default Structure is ticked, so type a password (use the same one if you wish) and click OK to finish.

Enter a password in order that only you can edit the quiz and, if applicable, make sure only ‘Select unlocked cells’ is ticked before you click on OK. Re-enter the password and click on OK again. Notice now that only the answers column can be clicked. To complete the job, choose Protect Workbook from the same menu or ribbon as before – by default Structure is ticked, so type a password (use the same one if you wish) and click OK to finish.