Here are three different ways you can create drop down lists in Excel along with their pros and cons:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
In this post I am going to show you the pros and cons of using three different ways to create a drop down list:
– Data Validation
– Combo Box using Form Control
– Combo Box using Active X Control
We have a list of the countries of the world. I’ve named that list “Countries”. I’ve set up three fields where I want the results of the drop down list choice to end up:
I am not going to go through the whole process of Data Validation. If you’d like to see more details on this process, please see some of my posts below:
Data Validation Part 1 – The List
Data Validation Part 2 – Dates & Values
Photos Tied To Drop Down Lists
Data Validation Circle Invalid Data
Limiting Survey Choices With Data Validation
Dynamic Data Validation Lists
Hyperlinks Using Data Validation
Data Validation With Limiting Choices
Data Validation With Tables & INDIRECT
Go to the Data tab and select Data Validation. Fill out the dialog box as follows:
The result will be the following:
Notice how the choice selected appears in the formula bar. In this way, you can use the selection in other formulas or parts of your workbook.
– Simple and direct
– No additional cells or formulas needed
– Can’t control font type or size
– Can’t control number of items that appear in the drop down list
– Only can enter directly into the cell with the drop down if you type the entire entry correctly
– There is no visible indication of the existence of a drop down list unless you click on the cell where it is located
You will need to have the Developer tab available in your Ribbon. If you do not have the Developer tab, go to File => Options => Customize Ribbon and make sure the box is checked for the Developer option in the right column:
Now, go the the Developer tab and click on the Insert option in the Controls group and select Combo Box from the Form Control section:
Go to where you want the drop down box to be and “draw” in the rectangle. If you hold down the Alt key while doing this, it will define the rectangle to stay within column widths and row heights:
Notice how I created the drop down list next to where I want the resulting country to be. The Combo Box options allow you to create a drop down and tie the selection to a cell. In this way, whatever you choose from the drop down list will appear in the designated cell.
Now, right-click on the drop down list and select “Format Control…” and fill out the dialog box like this:
You can see that we’ve tied the drop down list to a cell where we want the selection to go. Notice that we can also choose how many options we want visible when we click on the drop down arrow. In this case I chose 4.
However, when I make a selection, I don’t get the resulting country, instead I get the row location where it appears on my list:
In order to compensate for this, I need to select another cell that I want the drop down list to be associated with (F6 instead of E6), and create an INDEX formula that identifies the row number with the appropriate country:
– Can control the number of items in the drop down list
– Can size the drop down list as needed
– Drop down arrow is always visible to the user
– Many extra steps including selecting another cell tied to the drop down and needing an INDEX formula
– Can’t type directly into the drop down list
– Can’t control font type or size
– Far more complicated than Data Validation
Active X Control:
Initially, this option is similar to the Form Control Combo Box. Click on Insert, then select the Combo Box option under the Active X Control section and draw the drop down list where you want it to be.
Now, you can right-click on the drop down list and choose Properties, or left click on it and click on the Properties option in the Controls group, and the following will appear:
You now have a huge list of options as to how you can structure your drop down list. Note that I’ve entered E9 in the “LinkedCell” field, and “Countries” in the “ListFillRange”. If you click on the three-dot button at the right of the Font option, you can choose the font style, size and many other variables.
Here I’ve chosen the Cambria font in bold and 18 point. I can click on the drop down arrow to select my country, or, in this example, I’ve typed “Alg” directly into the drop down list and Excel had determined that I want Algeria, and has already populated cell E9 with that choice:
– Can control font size and style along with many other creative aesthetic variables
– Can control the drop down list size
– Drop down arrow always visible to the users
– Does not require tying to a different field and creating an INDEX formula
– Can type directly into the drop down field and it will anticipate the selection with each letter
– Requires tying drop down list to another cell
One last point, for either the Form Control or Active X Control option, you can move the drop down on top of the related cells and still be able to reference those cells in other formulas or parts of your workbook as such:
So there you have it – three choices for drop down lists. I hope this was educational and helpful in you choosing wisely.