Activity: Excel – Drop Down boxes



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.)

1. 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.

2. 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?’

3. 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.)

4. Add a further two text boxes and place near to the printer (Q4) and keyboard (Q5).

5. 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

1. Select the Answers tab. Two sets of answers have already been provided for you.

2. In cell F5 type Select from. In cells F6: F10 enter the following options:

a. Joystick

b. CD Drive

c. Speaker

d. Mouse

e. Scanner.

3. 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

1. Below the answer columns are two rows titled:

a. Correct answer: Add the number (shown to the left of Answer 1) that maps to the correct answer. Follow the examples given.

b. Student answer: The answer will be added when setting up the dropdown box.

1. Add the correct answer in cells F13, H13 and J13.

2. 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

1. Open the Forms toolbar, if not already visible. Go to View > Toolbars > Forms.

2. 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.)

3. On the Forms Toolbar, click on Control Properties icon. A Format Control dialogue box opens. Select the Control Tab.

4. The Input range is the reference to the cells where the answers are found

5. Click on the coloured square at the right hand side. The box below will be displayed.

6. The cell references for your answers to this question should be added to this box as follows:

a. With the box still visible, click on the Answer tab of the spreadsheet.

b. Highlight the answers for Question 1, these are found in the column titled Answer 1.

c. The cell range will be displayed in the format control box.

d. Click on the coloured square on the right-hand side of the Format Control box.

e. The Format Control option box will now re-open.

f. The Cell link is the cell reference of the learners answer and enables feedback to be given.

g. Click on the coloured square at the right hand side of the cell link box.

h. The Format control box will minimise as before.

i. Click on the Answer tab.

j. 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.

k. Click the coloured square at the right hand corner.

l. The Format Control option box will now reopen. Confirm OK.

m. You should now repeat this process for the rest of your questions.

Need help? Look for the video tutorial showing steps 2-6 on your E-Guides USB memory stick. Go to Content creation > Animations > exceldrop.swf.

Providing feedback

A nested statement is inserted on the Answer worksheet which checks the conditions between the students answer and the correct answer.

1. In cell B16 type the following IF statement:

=IF(B14=1,"",IF(B14=B13,"well done","Sorry, try again"))

2. 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.

3. Click on a cell beneath the Combo box.

4. Click into cell C10 on the Questions tab and enter the following formula: =Answers!B16.

5. 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.

6. Add the following formulae within the Questions tab.

f. In cell K9 enter the formula: =Answers!D16

g. In cell K25 enter the formula: =Answers!F16

h. In cell K17 enter the formula: =Answers!H16

i. In cell C24 enter the formula: =Answers!J16

Feedback – Additional information

There are three possible results:

a. When no answer has been selected. The student’s answer will be seen to be 1.

b. When the student has the correct answer,

c. 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

• IT Skills Level 1:

• 3D Projectiles:

• Parts of the Brain:

• Demand and Supply:

• Social Trends across Brighton:

• Scaffolding quiz:

• Dice teaching resource: .

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download