3 Types of Excel Drop Down Lists Compared

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.  It had some limitations, so here are 3 types of Excel drop down lists compared.

Data Validation Drop Down

At my client’s office, 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

Drop Down Types

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 Limitations

Data validation is a great feature, and you can use creative formulas to create flexible lists, such as dependent drop down lists. However, it has limitations:

  • the font size can’t be changed
  • only 8 rows are visible at a time
  • 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

Also, 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.

Get the Item Name

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.

____________

57 thoughts on “3 Types of Excel Drop Down Lists Compared”

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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.

  6. 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.

  7. 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!

  8. 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!!

  9. 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

  10. 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?

  11. 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.

  12. 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

  13. 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?

  14. 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.

  15. 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?

  16. 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?

    1. 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

  17. 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

  18. @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/

  19. 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??

  20. @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.

  21. 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?

  22. 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

  23. Pingback: Excel worksheet interactivity with ActiveX controls - JP Software Technologies
  24. 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

  25. 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.

  26. 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?

  27. 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

  28. 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

  29. Hi Debra – I would love to be able to use the data validation with the combo box and downloaded your files, but the code doesn’t run. I have checked all of the settings I know of. I write and run macros every day in excel. I also created my own spreadsheet from scratch and it still doesn’t work. Any suggestions? Thanks!

  30. Hi everyone,
    I would like to create a click button in excel. I have a drop-down list but I would like to add a button to it, if I click it, it will go down the list until I choose which value to leave it in the cell. Is there a way to have the button look in this drop down list?
    Thanks.

  31. I am using a Combo Box to enter data from a predefined list. My problem is that I do not want the selected data to be shown in the Cell link but in the active cell. Can this be done?
    Thanks in advance.

  32. I have created a drop-down box using Excel’s “Validation” “List” option, is there a way to select the name as one types in the cell…for example…i have values, like Armenia, Holland, United Stated of America, India, Indonesia, etc…for example I would like to select the value Indonesia for this cell….as I type “I” the system should be intelligent enough to filter me values in “I” until i finish typing “Indo” it should display me “Indonesia” can this functionality be done in excel using the Validation logic?
    Appreciate anyone’s assistance in this regard – Mike

    1. Mike, did you ever get an answer to your question? I need the same exact help. Oh, I just saw your post was back in 2015 so maybe nobody is answering this blog anymore. 🙁

  33. Pingback: show more than 8 items in the Data Validation drop down list in Excel – Internet and Tecnnology Answers for Geeks
  34. Hi Debra
    Thanks for sharing. Just wondering if you know the answers to these questions.
    1. When I do a spreadsheet with a data validation drop down list and try to embed that spreadsheet on my website (from onedrive) the drop down menu doesn’t work. Do you know why this would be? Would doing a drop down list from a combo box work with embedding?
    2. I tried to do a combo box drop down list but my excel doesn’t have the developer tab like shown in your screenshots. Is my excel not able to do combo box lists?
    Thanks for your help,
    Nathan

  35. Hi,
    I want to use a list in a drop-down box, but then have the user be able to select an item in the list or enter in another word. For example, a list of the usual people: Jones, Smith, Johnson, Cooper are in the list, but a substitute was there one day, so the user should be able to add: Hammond, which becomes part of the list now. I’m using Excel 2013…but I’m editing a spreadsheet created in omg old version, like 2007 or something… Maybe I just have to create new ones in 2013?
    Thanks!

  36. Hi, I’m currently trying to do something in excel that I’m unsure if it’s possible. What I’m trying to do is create a drop-down box in individual cells that allows a person to either use a dropdown or type/search function, but at the same time, am trying to find a way that’ll auto-fill other adjacent cells in correspondence to this input. I would also like the options to not be visible on the spreadsheet until the cell is pressed or typed on, as there are several rows of data that I’d like to perform this to.
    If you know anyway on how to do this, or need any clarification, could you please let me know? Cheers!

  37. I am using your Combo Box with VBA programming for double click dropdown lists and it is working but the combo Box has no list in it. can some won assist me. I am using the code provided from this post for Combo Box & VBA for Merged Cells.

Leave a Reply to Susan Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.