Entering data into Excel can be tedious and confusing to users. Fortunately, you can simplify the process. You can use drop down lists to limit data entry options to just one list of items. (Not to mention they just make you look that much cooler.)
In just a few steps, you can create three different types of drop down lists in less than eight minutes:
- A simple drop down menu
- A combo box (Stay with me, it’s not as scary as it sounds.)
- A more advanced drop down list
Drop Down List with Data Validation
This technique is really best used when you need to enter in the data from a preset list of entries. For example, it’s possible to write a month in several ways: January, Jan, 1/2013, etc.
Before making your drop down, you need a reference list. I like to use a second sheet, then just hide that second tab later. I’ve seen other people put the reference list on the same tab, but I find that if you insert any columns or rows near that list, it can mess up the referencing.
After making a separate list of months (A4, A15), name the range. To do this, simply select your list and type a name in the dialogue box next to the formula bar.
After naming the range of cells, go back to the first sheet and select the list of values you want in the drop down list. Under the Data tab, find Data Validation. Select the option List, and then enter the source. This would be the range named months.
Annnd there you have it! All the months in a drop down list.
Form Control Combo Box Drop Down List
Form Control drop down lists are more customizable than simple data validation. These boxes give you the option of overlaying a drop down box anywhere in the document. This form control will output the option selected in the box to any particular cell. You can also change the size of the box and the number of options viewable when selecting from the dropdown. (A quality not found in data validation.)
Before being able to work with Form Controls, you must activate the Developer tab. (We fancy, huh?) To do this, right-click on the Excel toolbarÃ¢â‚¬”also called the RibbonÃ¢â‚¬”and select Customize the Ribbon. Locate the Developer option on the right hand list of Main Tabs. Select OK, and you’re good to go.
Next, you can move on to adding a Form Control Box. You do this by going to the Developer tab and selecting Insert > Form Controls > Combo Box. Make sure you don’t select the ActiveX Control Combo Box. We’ll get to that craziness later.
When you select the Combo Box, you will be given the option to drag out a box on your spreadsheet. I recommend making your Combo Box slightly larger than the cell you want to place it over. After placing the Combo Box, right-click on the box and select Format Control.
Pro Tip: Turning the Gridlines on before placing your box will help keep it looking proportionate to the other cells. Just remember to turn them back off!
Now we get to the really fun part. First, navigate to the Control tab in the Format Object box. Then, select your input range. Mosey on over to the cell link and select the cell that you placed your Combo Box over. This will allow you to output the source behind your Combo Box. So let’s say I were to select Google as the option in my box. Then the Combo Box would output the word, Google, in whatever cell I chose. Pretty cool.
Lastly, select the optionÃ‚Â Drop down lines,Ã‚Â and change it to ten instead of the default, eight. This will allow you to see all the data validation options in one drop down without having to scroll. Alright! Now you are officially ready to move on the biggest of dogs, ActiveX controls.
ActiveX Control Combo Box
ActiveX combo boxes are the most advanced drop down option in Excel. You not only have all the other advantages of data validation and Form Controls (custom size and change the number of options viewable). But you also have the option to change the color of the combo box and format the text. This can really add some pizazz to your spreadsheet.
You’re going to start this combo box similarly to how you started the simple data validation. This time you are going to name the Source range on Sheet 2. To do this, select the range (A19,A28) and then go into the dialogue box next to the formula bar and type in a name. (Keep in mind you can’t use any spaces or special characters when naming a range.)
Under the Developer tab, select Insert > ActiveX Controls > Combo Box. Selecting Design Mode allows you to edit ActiveX controls. Without it selected, you cannot right click on or edit your combo box.
WithÃ‚Â Design Mode selected, right click on the combo box you just drew and select Properties. A very intimidating dialog box will appear. But stay with me on this oneÃ¢â‚¬”it’s easier than it looks. First, locate the option ListFillRange and type in the name of the range. Then go to LinkedCell and type in the cell you want the output to go to.
You also have the ability to change the number of options viewed. Under ListRows, input the number of options you would like to see. I love ActiveX Controls because of all the formatting options. You can change the text color, typeface, and size for the drop down options. You can’t use these options with form controls or data validation.
When in Design Mode, right click on the ActiveX Combo box and select Properties. Then select BackColor to change the background color of the combo box. Then select ForeColor to change the color of the text.
Next, you can change the typeface and the size of your combo box’s text. Select the FontColor box and click on the ellipsis to open the text dialogue box. Once you’re finished formatting, select OK.
Now turn off Design Mode, and there you have it! You have now mastered three different types of Data Validation. I hope you find this helpful. Feel free to experiment with these options and figure out which one works best for you.
Bonus: Download this template and feel free to duplicate. Have questions? Leave them in the comments below!