3 Types of Excel Drop Down Lists Compared

At a client's office last week, I was selecting a pricing option from a data validation drop down list. The worksheet was zoomed to 75%, so we could see more of the data, and the person watching over my shoulder asked, "How can you even read that?"

Good question. Sometimes the font in a data validation drop down is so small that you can barely read the list.

DropDownType00

Unfortunately, there's no way to make the font size bigger, which is one of the drawbacks of a data validation drop down list. There are other ways to create a drop down list though, and one of those options might work better in your worksheet.

We'll compare these types of Excel drop down lists:

  • Data Validation List
  • Form Control Combo Box
  • ActiveX Control Combo Box

DropDowns01

Data Validation List

Data validation is a great feature, and you can use creative formulas to create flexible lists, such as dependent drop down lists. However, the font size can't be changed, only 8 rows are visible at a time, and only the active cell shows a drop down arrow.

You can't change those settings, but you can colour the data entry cells, to make them obvious to the worksheet users.

Form Control Combo Box

Instead of using data validation, you could use a Form Control Combo Box. It gives you a bit more control over the appearance of the drop down list.

DropDownTypes03

You can adjust the number of drop down lines in the Form Control Combo box, so you can show all 12 months, without a scroll bar. You can also link the combo box to a cell, so the selected item number appears on the worksheet.

DropDownTypes04

The drop down arrow is always visible, so the Form Control combo box is easy for users to find on the worksheet. The font size can't be changed though, so the list would be hard to read on a zoomed worksheet.

DropDownTypes05

You can't type in the box, so click the arrow, then select an item from the list. That item's index number (its position in the list) is entered in the linked cell. In the screen shot above, February was selected, and a 2 is entered in cell D5.

To show the item name, instead of the item number, you can use an INDEX function in another cell. In this example, the INDEX function returns a month name from the MonthList named range, from the row number (2) for the selected month (February).

DropDownTypes06

ActiveX Combo Box

Another drop down list option is to use an ActiveX combo box. This is similar to the Forms Control combo box, but has more properties that you can adjust.

DropDownTypes07

After you insert a combo box, right-click the combo box, and click Properties.

DropDownTypes08

You can set the properties, such as font, font size, number of rows, and even include multiple columns.

DropDownTypes09

If you know that a worksheet will be zoomed most of the time, you can use a larger font, to make the list easier to read.

DropDownTypes10

This combo box can also be linked to a worksheet cell, and the value is entered in that cell, when you select an item from the list.

Also, you can type in the combo box, and the text autocompletes as you type, finding the first matching item in the list.

Combine the Drop Down Lists

To make the most of the data validation features, while allowing font changes, autocomplete and more rows, I sometimes combine data validation with a combo box. There's only one combo box, hidden at the top of the worksheet, and bit of programming makes the combo box appear when needed.

When a user double-clicks on a data validation cell, the combo box appears, and displays the data validation list for the selected cell. If users don't want to use the combo box, they can simply click on the arrow in the cell, and use the data validation list.


Watch the Drop Down Lists Video

To see a demonstration of the 3 types of drop down lists, you can watch this Drop Down Lists video.

Or watch on YouTube: Compare 3 Excel Drop Down List Types

____________

41 comments to 3 Types of Excel Drop Down Lists Compared

  • sam

    You forgot there is one more type of dropdown list
    Just type a list starting from A2 to say A10
    Hide the rows.
    Select A1 and type Alt+Down arrow OR rightclick button +k

  • Hi Debra,

    As it happens, I have published an article about worksheet controls quite recently:

    http://www.jkp-ads.com/articles/controls.asp

  • Jeff Page

    I don't have a comment. I'm just looking for: "Notify me of followup comments via e-mail". Won't let me leave a blank comment, so now I'm explaining. Let's see what works.
    Thanks
    Jeff

  • Jason Morin

    While I wouldn't recommend it, one option for Validation is to magnify the worksheet when the user clicks on the cell containing the validation. Something like:


    Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address(False, False) "A1" Then
    ActiveWindow.Zoom = 100
    Else
    ActiveWindow.Zoom = 200
    End If

    End Sub

  • JP

    LOL I've got a post scheduled about the various ActiveX controls. I'll send a trackback when it's published.

  • @sam, thanks for the reminder about the Pick From List feature.

    @Jan Karel and @JP -- obviously, great minds think alike!

    @Jason, thanks for the code sample. That's similar to the example that I have on the Contextures site, and isn't a perfect solution, but can help in some situations:
    Make the Dropdown List Appear Larger

  • Kanwaljit

    Hi Debra,
    I feel that many a times the width of dropdown box is similar to that of the column which contains the validated cell, but many a times it is much wider than that. Is there any specific reason for that ?

    Thanks

  • Hi Kanwaljit, I haven't found a reliable fix for the problem, but there are a couple of suggestions on my Contextures website:
    Data Validation List Too Wide

  • Hello,

    Does anyone have an idea of using dependent drop down list from another workbook?

    I could only get a list but not dependent list.

    Note: some people may say that why don't you use dependent list in one excel sheet... As per the company policy, changing data means we are going to have long time to get it approved and though we have to change our document revision number everytime that data gets increased. that's why we need it in different workbook.

    Thanks.

  • Redha, instead of a dependent list based on another workbook, you could use Microsoft Query to connect to the list in the master workbook. Set the connection to automatically update when the file is opened, and users will always have the latest version of the list from the master file.

  • Thanks Debra.

    I've used VLookup and it works fine and no need to open both of the workbooks... but I'll also try microsoft query as well although never used it before on excel sheets!

  • Maria

    Hello all,

    maybe you can help me, I have a question related to this discussion. If I use form control, is it possible to create dependent list?

    I know how to create dependent list with Data Validation (using INDIRECT reference), but it doesn't work when I use a form control. I want to use a form control, because I also need to get the value of my selection in a linked cell.

    For example, I have a dropdown list with all car brands, depending on the brand that I selected, I can choose the correspondent models to that brand in a second dropdown list. And if for example, I select the third model in the list I want to see "3? in a cell.

    thank you very much in advance!!

  • Norm

    Need a little help, new to excel but with its flexability I need it...all I want to do (famous last words) is create a drop down box to show items in col. 1(I know how to do a drop down box) and when selected I want to show items I have in col. B Col. C and Col D.
    Example;
    A B C D
    code error meaning countermeasure

    When a selection from the drop down box (A) is selected, the corresponding data in col. B through D will be displayed...

    Can you help me..
    Norm

  • Norm, you could use a VLOOKUP formula in the adjacent cells, similar to this Excel order form

  • Clifford

    We have a client data base that has about 1000 entries and it takes a while to scroll through the list each time. I am trying to update where the user can start to type the customer id and it pulls up possible selections based on the first couple of letters or numbers entered. Is this possible?

  • Carolyn

    Using the activeX control, can I make it work like option 2 – choose an item off the list, and make a number appear in the linked cell? I need the activex because I need to the font bigger, but want a corresponding number in the neighboring cell, not the words.

  • Shona

    Hi All

    I was wondering if someone would kindly help me. I am creating a Userform a need to create a dependent list in a combobox i.e. When a category is selected only items that relate to that category are displayed in the list of choices. I can create dependant lists using data validation in a worksheet but cannot translate this to a Userform. Is it even possible to do this?

    Any help provided would be greatly appreciated.

    Thanks

  • Susan

    I have question on the Data Validation with a Combo Box. I created it in my spreadsheet (thanks for the instructions!) but am running into issues. Users can type their own entry, and there is no error message after they move to the next cell. How can I get an error message to force all entries to be from the list?

  • Bill

    I feel like a dummy after reading some of the great questions above, but I'm going to ask anyway. I have been trying to create a drop down list in my spreadsheet. I have followed all the right steps, checked the "In-cell dropdown" box, yet it will never show the dropdown. It will not let me enter invalid values (ones that aren't aren't on the list I specified); it gives me an error message for that. But I cannot see the list from which I am choosing. I have been wrestling with this for hours. Any ideas? I am using Excel 2003.

  • Bill

    Thank you! The Hidden Objects option fixed it. I am going to bookmark this site!

    Best,

    Bill

  • Tony

    Is there any way to include a drop down list in a formula. In my example I want to return a specific value if the value in an adjacent cell is no ut if the answer is yes I want it to return a drop down list. Any ideas?

  • Chun Lee

    Hi Good day! Can someone shed some light on how to trace the dependents of the drop down list that is created using form control and ActiveX?

    • Peter D

      Ihave a similar issue. In a workbook I have received, the drop down list goes to #12 whereas I need to change it so that it goes to #17. How do I find the table or list where #1-#12 are located so that I can extend it? Thanks

  • Kate

    HI,

    Thanks for this helpful post. I've followed your instructions and created one drop down list. However, I need to create a second dropdown list, so that I can view multiple selections in both lists, but the second list is dependent on the first.

    So, for example, if you had Continents in the first list and countries in the second list, you could have "Asia" selected in the first list and "China, Japan and Thailand" selected in the second list (but you could have Europe selected in the first list too, with the sub-lisst of selected European countries visible below the sub-list of Asian countries).... Any ideas?

    Thanks in advance

  • Jeff Weir

    @Kate: http://www.contextures.com/xlDataVal02.html
    note that prior to Excel 2010, this wouldn't handle DYNAMIC ranges i.e. woudn't automaticaly include things that were added to the lookup lists. But if you have Excel 2010, you can get around this by turning your lists into Excel tables.

    There's a good writeup at http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/

    If you want a VBA approach, you can also try these:
    http://www.cpearson.com/excel/excelM.htm
    http://siddharthrout.wordpress.com/2011/07/29/excel-data-validationcreate-dynamic-dependent-lists-vba/

  • Vaibhav

    Hey Debra, I have two linked dropdown lists (selection in 1 generates the content for the other). My problem is that when the second list has only 1 entry, the macro associated with it is not triggered. I suppose it is because there is no change event happening when there is only 1 item. Is there any fix to this??

  • Jeff Weir

    @Vaibhav Here's 2 suggestions:
    1. Add a 'Choose from dropdown' item to the 2nd (slave) list as well as the first (master) so that 'Choose from dropdown' shows as the default option for both. THen your 1 item list becomes 2, and the macro will be triggered every time.
    2. Or do this: As well as triggering the macro by change of the slave list, also conditionally trigger the macro from the first (master) dropdown, using a SELECT CASE statement to run it when a user selects items with only one thing in the sublist.

  • Vaibhav

    Hey Jeff. Thanks a lot it works now.

  • Keith Harrington

    Maybe someone can help me on here. I need to make a mechanic schedule worksheet. Where in one column I have the initial start date, next column I have the action either annual, quarterly, and semi annual. And the final column I want it to compute the next due date automatically how would I do that?

  • Heidi Green

    How do I show the entire contents of a lookup list in an excel spreadsheet on a workbook/monthly timesheet? I have expanded the width of the column on the lookup list, but it seems that the width on the preset spreadsheet will not expand. Any suggestions? Or is there a way to attach the lookup list to the spreadsheet if not? thanks

  • [...] watch the other controls adjust themselves!Debra Dalgleish wrote about combo boxes on her blog (see 3 Types of Excel Drop Down Lists Compared ), and Jan Karel Pieterse has an article about ActiveX controls as well. Do you know of any other [...]

  • Maggie A

    Will either of these 3 drop-down list's enable me to choose more than one item in the drop-down list. Example I want the user to be able to choose apples and oranges not just one or the other.
    Thanks for your help.
    Maggie

  • Swami

    Hi, Im trying to create a sheet which has the items which i created as a drop down list. I also do want a description to add to particular item and have aprice for each descriptions. See ex below:

    DESCRIPTION ITEMS PRICE

    Development cost per set of job $802.00
    Reticulation Cost
    What I want to achieve is, when I select Development cost from the drop down I should only see per set of job,& reticulaton cost from the drop down. Based on the Items that I select I want the price to be shown automatically in another column. Please advise. I have tried DV with SUBSTITUE & INCORRECT commands. DIdnt work. any suggestion as I have a list of 150 description and around 400 items to populate.

    Thanks in advance.

  • Dea5r Sir,

    I need to increase drop doen list font in data validation list .

    Regards,
    Mundhe
    +91 9561094712

  • Chuck Driscoll

    I'm using an Excel spreadsheet for a customer questionnaire, utilizing drop down boxes so they can choose the answer (1 - 5) that they want. I need to make some changes and need to capture the drop down box entry (number from 1 to 5) as a literal so I can get the automatic grading sheet to operate properly. Do you know of a way to do that?

  • nicholas

    Excelent work! Thanks!

  • latino1

    Is it posible to combine drop down and formulas in excel?

  • Usamah

    how can i select word in drop down list just by pressing the first letter of the word, every time i have to select by click
    e.g i want to select the word "RELATIVES" when i press the letter "R" it come automatically

  • Laura

    How can I set a spreadsheet up to allow changes to a cell for job site project# scheduling/tracking when a new field/selection is made from a form control combo box drop-down list that contains the months of the year?
    I have created an automatic calendar with Excel 2010 that allows in hopes to schedule employees and the project# for sites they will work for a given day. However, when I change the month the data entered for a certain date will remain in the same cell. I need to be able to go back and forth between different months so I can track where our techs have been. I followed the directions/formulas in the below link and noticed others have had the same problem but I couldn't find a solution to the problem... I appreciate your time in this matter; thank you! https://www.youtube.com/watch?v=yY9AbS_ehb0

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>