Options!

In the old Clint Eastwood movie, Magnum Force, Harry Callahan used the line, “A man’s got to know his limitations”. True as that is, Harry always liked to have Options. For this first post of 2012, I thought it would be interesting to take a look at Option Buttons.

Option Buttons can be very useful in creating Quizzes or Polls in Excel, and are not too difficult to create. There are a few steps, but I guarantee that it will be worth the effort! Here is how you do it:

1. Make sure the Developer tab is visible on your toolbar

2. Under the Developer tab, choose Insert / Option Button (Form Control)

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 (Form Control)

5. Then draw your Group Box all the way around your Option Buttons

Hooray, We’re Almost There!

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 the Fun really begins. 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(E5=3, "Correct!", "Incorrect"))

Now when the user chooses Option 2 of the three possible, they are rewarded with the “Correct” feedback. Option Buttons, give them a try; “It is Always Good to have Options…”

Happy New Year All!