Thursday, July 14, 2016

Quizzes in Excel

Only a very small percentage of Excel users ever get around to exploring Form Controls.  This is unfortunate, as they can be instrumental in creating engaging, dynamic Excel workbooks and tools.  Option Buttons, (one the tools contained in Form Controls) are one way to add this ability to your Excel creations, and make them more professional in the process.

Option Buttons can be very useful in creating Quizzes in Excel, and although they can at first appear a bit complex, they are in fact quite easy to create.  The following is a Step-By-Step Explanation of how to create your first quiz using Option Buttons:

1.   Add the Developer Tab on your toolbar:

a.   Click the File tab and then click Options, and then click the Customize Ribbon category.

b.   In the Main Tabs list, select the Developer check box, and then click OK

2.   Under the Developer tab, choose Insert/Form Controls/Option Button

3.   Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices

4.   Now, (this is Important), to make the buttons work together, return to your Insert dropdown and choose Group Box from the Form Control group

5.   Draw your Group Box all the way around your Option Buttons

Hang in There, We are Almost Done:

6.   Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate

7.   Now is where it get Fun: Create a Formula that is based on the value that is shown in the Cell Link

8.   For example, let’s say you have linked three Option Buttons to Cell $E$5 and you wanted Option 2 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell E6, type =IF(E5=0,"", IF($E$5=2, "Correct!", "Sorry, Incorrect"))

Now when the quiz-taker chooses Answer 2 of the three possible, they are rewarded with the “Correct!” feedback.  Option Buttons, a remarkably forthright tool that let’s you do Even More in Excel!

No comments: