Today I'm going to show you how to make a quick and easy multiple option drop down list in Microsoft Excel 2007 (works in other versions too). Drop down lists are useful when you want to limit a user's entries to only values that you specify. I've showed you earlier how to create a drop down list using Named Ranges, which takes a few extra steps but offers more repeatability. Now I will show you a much faster and simpler solution for creating these types of lists in Excel.
First, begin by selecting the cell you would like to add the selectable list to. Next, go to the Data tab on top and select Data Validation. A window will popup. Here you will find from the list of pull down options the ability to change the Allow: feature to List. Now, under the Source type in the name of the values you want to appear in the pull down menu, which each term separated by a comma. Click OK when you are done. That's it!
If you click on the cell you should see an arrow appear on the right hand side of the cell. When you click the arrow you should see the name of the values that you entered in the Source box. You can fill down this cell to other cells you would like to contain the same pull down lists.
Also, you can convert your user entered Yes or No value into a numeric value for calculations using a simple IF formula,: If the user selects Yes then the value is 1, if No, then 0:
For more Excel spreadsheets tips please consider joining our newsletter.
Check out some of our recommended tools to learn Excel and how to write VBA macros.