Spreadsheets can be more than just numbers - Nick and Toni Peers
reveal how to have some fun with Excel
If you're looking for some light relief, then a quiz can often
prove a welcoming distraction. And when it comes to creating quizzes,
Microsoft Excel is
the perfect tool, offering you a win-win scenario. Not only do you get to have
some fun, you develop your Excel skills further too.
The simplest kind of quiz is a question-and-answer one. You
provide the questions and people type in the answers into a blank cell. Their
entry is then checked against the correct answer and they're awarded points if
they get it right.
The biggest challenge here is taking the person's answer and comparing it to
the correct one, but it's a simple task in Excel. Start by creating a
new worksheet. Use column A for the questions, column B for the answers and
column C to score. Format these accordingly so there's plenty of space for you
to type in your question and for people to enter their answers.
Build your quiz
Now go to cell A1 and type in your first question. Cell B1 is where
the user types or selects - if you follow the walkthrough below - his or
her answer. Cell C1 is used to check the contents of cell B1 - it's blank
until an answer is provided, and then the user is informed as to whether
it's right or wrong.
This is done with the help of a nested IF function, so type the following
into cell C1:
=IF(B1="", "", IF(B1="answer", "Right", "Wrong"))
Replace "answer" with the correct answer to your
question. The function works like this: if the contents of B1 are blank
then nothing is displayed. Once something is entered into B1 it's checked
against the answer, which returns the "Right" or "Wrong" message
accordingly. The IF function isn't case-sensitive, so typing in answer,
ANSWER or even aNsWeR would all be considered correct. |
|
How about keeping score? You could replace
"Right" and "Wrong" with 1 and 0, but that might confuse the person doing the
quiz. Instead, let's add another IF statement to cell D1:
=IF(C1="Right", 1, 0)
Now, repeat the procedure for all of the other questions in
your quiz, remembering to change the cell references accordingly (so cell C2
refers to B2, D2 to C2 and so on and so forth).
Finally, how about displaying the total score? Let's assume you've
written ten questions in cells A1-A10, so type the following into cell D11:
=SUM(D1:D10)
This function tots up all the points awarded in the first ten
questions and displays the score in cell D11. For more customisation tips,
check out the box at the bottom of this tutorial.
| Create a multiple-choice quiz |
|
1 Write
multiple answers
To give people four choices per question from a drop-down
list, switch to Sheet2. Type the answers to question
one into cells A1:A4, question two into cells A5:A8
and so on. Select cells A1:A4, type Question1
into the Name Box and press [Return]. Repeat for the
other questions, naming them Question2, Question3
and so on. |
2 Set up
Data Validation settings
Switch back to Sheet1, select cell B1 and choose
Data > Validation > Settings tab. Select
List from the Allow drop-down menu and type
=Question1 into the Source box before clicking OK.
Repeat the process in cell B2, but type =Question2
into the Source box, then finish off the other questions. |
|
3 Choose
colour depth
Return to Sheet2 and make a note of which cell contains
the correct answer to each question. Switch back to Sheet1
and - assuming cell A3 is question one's correct answer -
enter the following line into Cell C1:
=IF(B1="", "", IF(B1=Sheet2!A3, "Right", "Wrong")) into
cell C1
Repeat for the other questions in your quiz. |
EXPERT TIPS
Select column C and choose Format > Cells >
Protection. Tick Hidden and click OK to keep the
location of your answers secret
To quickly apply a style to your quiz, select the cells used and choose
Format > AutoFormat, then pick a table design
If you create headers in row 1, select cell A2 and choose
Windows > Freeze Panes to ensure they're always visible |
|
Customise your quiz
Once the mechanics are in place, give your quiz a striking new look |
Want to take your quiz to the next level?
First, how about making it more user-friendly? Start by adding an
introductory page with a welcome message and instructions - this can be
done by inserting a new sheet at the front of your spreadsheet
(right-click the Sheet1 tab and select Insert, pick
Worksheet and click OK).
Next, let's add some column headings. Move all of the cells in your
quiz one row down by selecting them all and cutting and pasting them
accordingly in row 2. Now type the following headings into cells A1:D1
- Question, Answer, Right/Wrong, Points. Style
these as you see fit - bold and underline should do the trick.
How about giving some feedback to the player based on their score?
Use a nested IF command like the following, which assumes cell D12
contains your final score:
=IF(D12<4, "Poor", IF(D12<7, "Average", IF(D12<10, "Good",
"Perfect!")))
If your quiz feels a little lifeless, how about basing some
questions on images or even WAV sound clips? You'll need to insert a row
above the question the clip refers to: select the row in question and
choose Insert > Rows to do so. To insert a picture, choose
Insert > Picture > From File; select Insert >
Object > From File tab for WAV sound clips. Once inserted,
drag the clip into place above the question and resize the empty row to
accommodate it. People can listen to sound clips by double-clicking the
icon, so don't forget to include instructions on your welcome page! |
Protect your answers
The quiz above works perfectly, but it does rely on honesty -
anyone can find out what the answer to the question is simply by
clicking in the relevant cell under column C, where it's revealed
as part of the IF function.
If you want to keep your answers secret, do the following. Switch
to Sheet2 and type in the correct answers in column A,
so A1 is the correct answer for question A1 on Sheet1 and so on
and so forth. Now return to Sheet1 and in cell C1,
change it so it reads as follows:
=IF(B1="", "", IF (B1=Sheet2!A1, "Right", "Wrong"))
This tells cell C1 to look on cell A1 of
Sheet2 for the answer. Assuming your answers are lined up in a single
column you should be able to copy cell C1 to cell C2 and its references
should update automatically to B2 and A2, enabling you to paste cell C1 to
other column C cells.
Of course, if anyone wants to look up the answers, they can view
Sheet2, so what you need to do is hide it from view. To do so, select
Sheet2 and then choose Format > Sheet > Hide. If
you ever need access to the sheet, just select Format > Sheet
> Unhide to access it again.
To protect your quiz further - so people can't
unhide hidden sheets check out the walkthrough below. It's designed for
Excel 2002 (XP) and 2003, but should work with Excel 97
and 2000 - ignore the reference to "Select unlocked cells" in step
two. |
|
| Protect your quiz |
|
1 Unlock
access to the answers column
Select column B. Choose Format > Cells,
switch to the Protection tab and remove the tick from
the Locked box before clicking OK. This enables
people to type in or select their answers without being able
to access the rest of the spreadsheet. |
2 Protect
Sheet1
Now choose Tools > Protection > Protect
Worksheet. Make sure the only box ticked is Select
unlocked cells. Enter a password at the top that only you
know, enabling you to edit this worksheet in future without
removing its protection. Click OK and re-enter the
password to confirm it. |
|
3 Extend
protection to whole quiz
To protect your hidden sheet you need to add protection for
the entire spreadsheet - select Tools > Protection
> Protect Workbook. By default the spreadsheet's
structure is protected, tick Windows to protect the
window size too. Enter a password and click OK, then
re-enter it as before to confirm it. |
EXPERT TIPS
If using images, sounds and other media, keep an eye on the size of your
spreadsheet, especially if you're distributing it via the Net
Tweak your images first in your image editor - rescale them and reduce the
colour depth to keep them as small as possibleThe
project works in all versions of Excel from 97 onwards |
| |
