Excel® - drop-down boxes
Why
use drop-down boxes?
Microsoft® Excel® drop-down boxes allow teachers to create simple multiple-choice exercises that can provide instant feedback.
Excel®’s Control Toolbox has additional functions to create sliders, check boxes and option buttons for a variety of interactive exercises.
How to create drop-down boxes in Excel®
The following instructions will help you create the simple drop-down activity shown above. (A video animation, showing steps 2-4, is available. See the instructions below.)
- Open the file computer_basics on your E-Guides USB memory stick: Content
creation > Activity
files > Computer_basics.xls. It contains
an unfinished version of an interactive exercise
based on the parts of the computer. Note
that the Excel® spreadsheet has two sheets:
one labelled Questions the
other labelled Answers.
- The first step is to set up the questions
and answers before adding the drop-down boxes.
Click on the Question tab.
An image and two text boxes have been added
already. Copy one of the text boxes and paste
near the mouse indicated in the image. Type
into the box your new question, ‘Q3.
What is this called?’
- Alternatively create
a new text box using the Drawing Toolbar.
Select the Text box
icon on the Drawing
toolbar and click and drag to draw a text
box where you wish to place the question.
(If you do not have the Drawing Toolbar
visible go to View > Toolbars > Drawing.)
- Add a further two
text boxes and place near to the printer
(Q4) and keyboard (Q5).
- Place an arrow against all three newly created text boxes, pointing towards the item. Copy and paste an existing arrow or use the Drawing Toolbar to create an arrow.
Setting
up the Answers page
- Select
the Answers tab.
Two sets of answers have already been provided
for you.
- In cell F5 type Select from.
In cells F6: F10 enter the following options:
- Joystick
- CD Drive
- Speaker
- Mouse
- Scanner.
- Complete the options for Question 4 and 5 in column H and J using the same method with various answer options.
Adding
in the correct answer
- Below the answer columns are two rows titled:
- Correct answer: Add the number (shown
to the left of Answer 1) that maps to
the correct answer. Follow the examples
given.
- Student answer: The answer will be
added when setting up the dropdown box.
- Add the correct answer
in cells F13, H13 and J13.
- Your spreadsheet is now ready for the dropdown boxes to be inserted. Before moving on, save the activity you have made. Go to File > Save As and give your completed activity a name.
Creating a drop down box
- Open
the Forms toolbar, if not
already visible. Go to View > Toolbars > Forms.
- Click
on the Questions tab on
the spreadsheet. Select the Combo
Box icon on
the Forms Toolbar. Click
and drag to create a rectangle to the right
of question
1. (Ensure it is large enough to view
a ten-letter word.)
On the Forms Toolbar,
click on Control Properties icon.
A Format Control dialogue
box opens. Select the Control Tab.
The
Input range is the reference to the cells
where the answers are found
- Click
on the coloured square at the right hand
side. The box below will be displayed.
- The cell references for your answers to this question should be added to this box as follows:
- With the box still
visible, click on the Answer tab of the
spreadsheet.
Highlight the answers for Question 1,
these are found in the column titled Answer
1.
- The
cell range will be displayed in the format
control box.
- Click on the coloured
square on the right-hand side of the Format Control box.
- The Format Control option box will
now re-open.
- The Cell link is
the cell reference of the learners answer
and enables feedback to be given.
- Click
on the coloured square at the right hand
side of the cell link box.
- The Format
control box will minimise as
before.
- Click on the Answer tab.
- Highlight
the cell where the student’s answer
will be placed for the question. The cell
reference will be placed in the Cell
Link box.
- Click the coloured square
at the right hand corner.
- The Format Control option box will
now reopen. Confirm OK.
- You should now repeat this process for the rest of your questions.
Need
help? Watch
the video tutorial showing steps 2-6 on
your E-Guides USB memory stick..
Providing feedback
A nested statement is inserted on the Answer worksheet which checks the conditions between the students answer and the correct answer.
- In cell B16 type the following IF statement:
=IF(B14=1,"",IF(B14=B13,"well done","Sorry, try again"))
- Copy this formula into cells D16, F16, H16 and J16.
The results of the IF statement need to be linked to the Questions sheet into the box next to Feedback for each question. This way the user only sees the immediate feedback from their choice.
- Click on a cell beneath
the Combo
box.
- Click into cell C10 on the Questions tab
and enter the following formula: =Answers!B16.
- Press Enter on the keyboard to confirm OK.
You can now experiment with the dropdown box to see the feedback for the correct and incorrect answers.
- Add the following formulae within the Questions tab.
- In cell K9 enter the formula: =Answers!D16
- In cell K25 enter the formula: =Answers!F16
- In cell K17 enter the formula: =Answers!H16
- In cell C24 enter the formula: =Answers!J16
Feedback – Additional information
There are three possible results:
- When no answer has been selected. The student’s answer will be seen to be 1.
- When the student has the correct answer,
- When the answer is incorrect
To provide feedback, the three possible results are combined in a nested IF statement.
The statement would be inserted into the appropriate Feedback cell, in the example given this would be in B16.
=IF(B14=1,"",IF(B14=B13,"well done","Sorry, try again"))
The first part of the statement looks at:
=IF( B14 = 1, then “”, which means no feedback, the “ “ provides a blank.
The second part of the statement compares the correct answer and student answer.
=IF(B14=B13,"well done","Sorry, try again"
If the answer is the same, it’s a ‘well done’, otherwise the answer is incorrect and the appropriate reply is shown.
You can change the feedback to use your own words.
Now that you have created a drop-down box in Excel® why not create another activity?
The following are examples of learning materials produced using Excel® drop down boxes and other controls. This type of activity can be created in the same way using sounds or video clips for students to choose answers from options.
Further resources
