Comments on: Create drop down list in Excel: static, dynamic, editable, searchable

Excel is good at organizing and analyzing complex data. One of its most useful features is the ability to create dropdown lists, which allow selecting an item from a pre-defined list. Continue reading →

Comments page 4. Total comments: 270

  1. Using comma dont work for list, i had to use ";" in between the values.
    Ex:
    Yes;No;Maybe

    Did MS change this? I'm using 2013

  2. If I have an EXCEL drop down list and I want to enter free form text in one of the drop downs, and let it be part of the drop down?
    Example : I have static drop down messages in cells A1 to A5. If I wanted to enter free form text in cell A6?
    Thank you.
    Sumant

  3. the list in "Target Book" works only when the "Source Book" is open.
    how i solve it

  4. Thanks. Great article

  5. how to add multiple formulae drop down list in on the cell where we can choose sum, min, max or more function .please provide me solutions.
    like cell a is heading and a2:a20 id numeric data but we need a list of formula where we can choose sum, min, max or more function.

  6. I have created a dependent drop down list. But when I click on the drop down arrow, it shows the values in reverse order. My sheet has 1, 2, 3 in numerical order. But the drop down shows 3, 2, 1. Any way to fix this?

  7. Hi Svetlana

    is it possible to link a value to a drop down list ie oven cure 1 = 5hrs
    oven cure 2 = 3hrs so this can be intergrated into a cure schedule

  8. hello Svetlana

    from the drop down list if i select some, it should display in the every particular selected cell of the column, can this be done, thank you..

  9. Hi Svetlana,

    Using with drop down list(sheet1, Sheet2, Sheet3), is it possible to jump (active sheet) to another sheet as described in drop down list.

    Thanks
    VIJAY

  10. Hi There,

    This has been super helpful in creating my drop down menu... my only wish is that my employees could start typing an address and have it come up from the drop down menu? We have multiple addresses we need them to select from on a daily basis but I thought there may be a way for them to, for instance, type into the drop down menu (almost like a search, 1343 w_____ would bring up 1343 Whitby- from the drop down menu?

    We are trying to make it quicker and more accurate for myself when entering payroll. so that the guys stop making typo's on their addresses when entering their timesheets to which job they were working .

    I believe DEE (see above) is asking the same question.

    Thank you for your help!

    Lindsay

  11. Hi Svetlana,

    I want to make a list of name with drop down list but searchable. Can you help me on this? Thanks.

  12. HI,

    Thanks for the detailing about DropDown.
    I need to know, what if, I don't want to leave the cell Blank.
    I want my cell to display 'Select' before selecting the option from drop down.

    Please suggest.

  13. Hello,

    I was wondering is there a way to create your drop list where in the drop down it has the Ingredient and description of Ingredient but once I choose my option it just display the Ingredient and not the description. For example, in the drop list (when the arrow is clicked on) it has:

    Eggs - yellow yoke
    Broccoli - green vegetable

    but once I choose Eggs, it just display "Eggs" in the cell and not "yellow yoke".

    Is it possible?

  14. Hi, i need help on drop down list issue below : -

    I got a file from others, there is cell containing drop down list and i need help how to edit the item name inside the drop down list because i couldn't find where is the source. Tq

  15. This is very useful to my office works.
    Thanks a lot.

  16. Hello,

    Thank you for this great source of information!

    I would like a second list to populate information depending on the selection of the first list.

    For example in the first list column A we have male and female. Second list column B has Pregnancy Test and NA. So if user selects Male then the only available item in list B should be NA.

    I've looked at the IF formulas but am unable to find the correct combination.

    Thank you for your advice.

    Troy

      1. Amazing! Thanks thats exactly what I needed!

        Best Regards,

        Troy

  17. This is incredibly helpful. But is there a way for more than one item on the list to be selected? For your example of the ingredients - would someone be able to select both flour and eggs? Or would you suggest two columns both with the same drop down list?

    1. Hello Roxy

      Excel's dropdown allows selecting only one item. So, in your case creating two drop down lists with the same items sounds quite reasonable.

  18. my drop down list contains the months of the year. when i click a different month can the body change to the corresponding month? I am keeping attendance. so when i click to december i want the record to start blank.

  19. Hello there, this is a great post! Thank you so much.
    I have a query - What if there are multiple entires of first Cell in the table with different dependant values?
    For Ex:
    Orange | Fruit | Tasty
    Oragne | Color | Good

    So the when I select first column as Orange, it should show up both Fruit and Color in second drop down.. but since you INDEX(), it only shows first match. (Fruit) .

    How to make multiple entries show up? Any help will be greatly appreciated.
    Again, thank you so much for the great blog.

  20. Excellent post!

  21. Hello, Svetlana
    Thank you, great blog if you could help with one more problem I would be grateful. I am trying to populate a data validation list based on a table that works as follows “Col A.” reps a State, “Col B.” City, “Column C.” Postal ZIP Code. No cells in table are blank i.e. A2:A5 = Mississippi then B2:B5 = Tupelo then C2 though C5 are different. Is there any way to make a Zip Code selection based on the City?
    The table has 9 States and 38 Cities and approximately 122 Zip Codes.
    Thank you again

  22. Hi, I have a dropdown list wherein it allows user to select their names that will reflect the time that they have finished doing the task. My question is, I want to prevent other users from selecting other people's name. in short, they should only select their own name. How will I do that?

  23. i need help. I have a combo boxes and I want to use it to be entry data into another worksheet serially. the post above is very simple and understandable. good work thanks

  24. Dear Svetlana Cheusheva

    This post is so nice and simple for me to learn how to do and useful for me to finish my work Thank a lot.
    Be successful in you work
    Best Wishes,
    Pilofito

    1. Dear Pilofito,

      Thank you so much for your kind words!

  25. I am trying to create a worksheet with each column being a month of the year but then making that month into 4 weeks within a drop down box. I have done that but when I go to change the weeks, the data does not change with the box I choose. I am not sure how to fix this.
    Please Help
    Patricia

  26. Hi..the post is really useful.. i have one small question..what do i so if i have multiple words in my list..for eg: if i have to type full names?

    1. Hello Swati,

      No problem, just type the names like usual with spaces in between in some range, and then create a drop-down list based on that range.

  27. THANK YOU!!!

  28. Your demonstration most helpful. Keep it up!!

  29. hi,
    i made a drop down list, but want to type the starting alphabet to select the item, but it is only working by pressing arrow.
    please help..

      1. Thank you Alexander,
        Please speak any other option if it is not possible in standard excel drop down.

        1. He has to write.

  30. OK I must be missing something.

    I have a data sheet with all my drop-downs built, unfortunately it only works on the data sheet. But on page where I need to use them on page One the old lists are still their. I want to remove the old lists and put the new list with/button on Pg 1.

    And I am forced to right click and choose from drop-down. Would like a button when I click on the field.

  31. Hi,
    I have created a yes no drop down cell but now I can't fill it with a colour.
    Can anyone help?
    TIA
    Carl

    1. Go to cell style to change color.

  32. interesting and helpful

  33. Sorry can tell Me About make Attendance For summery list excel
    thank .

  34. Is there a way to have a drop-down that is list of text descriptions, but once selected it is an ordinal number? For example, "Don't know", "Limited", "Meets", "Exceeds" and puts 0,1,2,3 so I can add the values. I am using this to provide a ranking but the ranks have different descriptions for the different columns of criteria.

  35. Hi,

    Quite a thorough tutorial, but I am trying to create a list where i can jump to specific item using keyboard in a long list (e.g. In a list of 1000 names I have to select a name starting with "t", how can i do that without scrolling from mouse)

    Regards,

  36. MY COMBOBOX KEEPS PUTTING TEXT IN A NUMBER CELL AND CHANGING THE CELL TEXT AND NOT A NUMBER. Is there a fix for this? It is a activex combobox I have ZIP codes being pulled up in a INDEX & MATCH =INDEX(info,MATCH(E10,ZIP,0),6) E10 is where I drop the zip code in. If I type the zip code in it works perfect but if you I use the combobox it changes all my fields to N/A (city,state,etc). Each time I use the combobox to put the value in E10 I get a error symbol next to E10 "this cell is formatted as text" there is a drop down to convert it to a NUMBER and once I do that all the N/A in the fields goes away. PLEASE HELP and Thank You

  37. How to put shapes instead of txet or numbers in drop down list in Excel?
    Thank you,

  38. I have a list of actors and list of their respective films in a sheet, in another sheet I have the column Actors and Films column. The user selects an actor from the dropdown list (about 4 lines long). Then a film is chosen from a dropdown list (about 15 lines long) of films about the chosen actor.

    I would like that , when a title film is selected this title will not be seen next time the user choose the same actor, but should be available for the next time the file is open or the 15 lines run out.
    Ideally a message should flash like "That film has already been chosen, try another one". Any help will be much appreciated. Thank you

  39. I want to make a drop down list for example on D3 1 to 5 by entering just 5 on B2 cell.

  40. I have the drop down list working. But, is there any way to be able to start typing until the correct choice pops up, move on the the next drop down list.

  41. Hello!
    I create a drop down list for 10 column and then I want to add more column but I can't insert or delete the column. The only way I can do is drag it down to more make more column. SO what's the problem? and what can I do to insert column beside drag down?
    Thank you.

    1. Hello, Pisey,

      For us to be able to help you better, please send a sample table with your data in Excel to support@ablebits.com. Please add the link to this article and your comment number.

  42. Hi,
    Is it possible in Excel that the value list (drop-down list) shows like (Value Description) which are in two columns, but when user clicks the value, only value is selected in the destination cell

    1. Hello, Farhan,

      Most likely you need a VBA for this task. Sorry we cannot help you with it.

  43. Hi,
    I am looking for a requirement like I have a,a,b,c,d,c,d in a column and now I want the whole columns unique values to be as a dropdown/list in another cell.
    Is it possible?

  44. Thank you very much

  45. Hi,

    I have created four cloumn names called a,b,c and d.I set Data Validation by selecting the Allow field as LIST and Source field as Yes,NO,N/A for B column.

    My quetion is here : Whenever I select "yes" from the drop down list in B column , it should show the Dialogue box as "Fill the coulmn C and D".

    Could you please let me know how to create this in excel.

    1. Hi Din,

      We don't know a formula for this task. Most likely you need a VBA macro.

  46. Hi,
    I created a Dropdown list, and it worked out well. But when I save the sheet, close and open it again I don't see the dropdown lists I created, and I'm not able to figure it out why, Please help me out.

    1. Hi Vicky,

      Please specify the format you use to save your workbook. Is it xls, xlsx, csv?

  47. Hello,
    These are really great tips. But is there an easy way to make drop-down list drop when clicking or double-clicking anywhere in the cell, not just on the arrow?

    1. Hello,

      You need a special VBA macro that will spot when you click on a cell and open the drop-down list.

  48. It is make some of schedule. for work or class. Thank you too much.

  49. It is make some of schedule.

  50. Many Many Thanks

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)