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 3. Total comments: 269

  1. I'm in the end. I didn't find solution to my problem with drop down menu.
    The problem and question is, how to update already selected values from drop down menu, after that I changed original values in source table for drop down menu?
    Because in Excel I have a list of hardware in many worplaces. And from time to time when workplace will change name I need to change it in Excel too.
    And easiest way is to change/edit it in table for drop down menu, not to change it by add new workplace to table for drop down menu and then find hardware and select new work place from drop down menu. But now, when aj change some values in table for drop down menu, this change is not reflected in my list.

    Just for sure: I'm not talking about adding new rows for drop down table.

  2. Great lesson indeed! Thank you. Is there a way of choosing more than one item from the drop down menu. I do not understand VBS or anything that is outside Windows at all!

  3. Good stuff!

  4. Hi,

    Awesome article!

    However, when doing the dynamic one I copied the code in and changed the sheet name accordingly. However, it will only give me 88 rows and anything after this gets cut off.

    Any idea what I am doing wrong?

  5. Dear all,

    I have a problem with defining the source of the drop down list.
    When I click on another sheet for referencing the table where the list is, I can't. Excel just annoys me with an error sound and nothing shows. It doesn't go anywhere. I can't reference anything. What am I doing wrong? What should I do to overcome this. I'm using excel 2007.

  6. thank you very much for your suport

  7. I want to create a drop down in a table form.
    eg. code budget actual variance percentage

  8. Great job on this link. I was able to create a list on one workbook referencing another workbook and it worked perfectly. I now use the list to select parts and then populate other columns using VLOOKUP. Thanks for the article.

  9. Hi,
    You are very helpful!!!
    I have a drop-down list with 8 items. When I open the drop-down, only 7 items appear and I have to scroll up to see the first item. Is there a way to always have the entire list show each time? I'm afraid my users will miss an option on the list.
    Thanks.
    Carl

  10. Someone created a table for me but I need to add items to one of the lists which is located on a separate sheet. When I add the items to the list, they do not appear on the dropdown on my input sheet; only the original list appears. How do I add extra cells? I tried to do DataValidation but it does not add them. Thanks

  11. Hi
    I have a Worksheet_SelectionChange event procedure in a worksheet which make drop-down list automatically by selecting a specified cell in a column. But, after creation some drop-down list and selection of other cells for new drop-down list creation, I couldn't find the previous dro-down lists location.
    Thanks

  12. Hello,

    How do you keep drop down box in the shared workbook. It works well in the unshared mode.
    Thanks

  13. Is it possible to create a conditional based data validation and how?

  14. Hi
    If I want to be able to change chosen list item in a list, can i do this non-vbs?

  15. Hi,

    I want to create a drop down list that when selected shows both the item number and description but once selected would only show the item number.

    For example, I would select the 2 - Blue but once selected, the cell would only show 2.

    Drop Down List
    1 - Green
    2 - Blue
    3 - Yellow
    4 - Brown

    Please let me know if it is possible.

    Thanks,

    Betty

    1. Hi, Betty,

      I believe you need a VBA code for your task. But I'm afraid we won't be able to help with that.
      I can only suggest you asking around Mr. Excel forum for it. They have a section there where they help with such codes.

      Sorry I can't assist you better.

  16. Perfect , Thanks a lot. now i feel like a genius.
    it worked well on office 2016.

  17. Hi,
    I am wondering if it is possible to use a drop down list which could be then linked to a column of data. For example, if from the drop down list i choose a value, for the next row I want to disallow the selected value from first row, or I want the selected values to disappear for the drop down list in the next rows?!
    Thanks in advance

  18. Hi Svetlana Cheusheva,

    This article is super easy and very good. I created the drop down option from Data Validation options and it works perfect - I don't have to enter the category again and again anymore.

    I have a question regarding drop down list and auto fill to a different cell. My excel sheet is as below:

    Column B= Date
    Column C= Store Name
    Column D= Cost in USD
    Column E= Purpose (This is where my drop down list is choosen from, the Validation criteria list is obtained from Column S (List has Rent, Grocery (Cell=T12), Utility etc)

    So, I would like the Grocery Cell (T12 in column S) to fill automatically and get the cost updated ( up to date cost). Such as below:

    Data--Store Name--Cost--Purpose
    07/01/2017--Walmart--$26.50-Grocery
    07/04/2017--Sams Club--$16.50-Grocery
    07/06/2017--Walmart--$26.25-Grocery
    07/10/2017--Costco--$6.50-Grocery
    07/11/2017--Walmart--$10.50-Grocery

    "Column S" & "Cell T12"
    After 07/01/2017 Entry - T12=$26.50
    After 07/04/2017 Entry - T12=$43.00
    After 07/06/2017 Entry - T12=$69.25
    After 07/10/2017 Entry - T12=$75.75
    After 07/11/2017 Entry - T12=$86.25

    Your help is very much appreciated and look forward to getting a result for this question.

    Thank you,
    Sam Arthur

    1. Correction:

      "Column T" & "Cell T12" value to be updated.

      1. Actually, I got the solution. Its just a formula to use instead of Vlookup. Thanks.

  19. 15 Nick Williams Jersey.Cheap 2014 New Cheap Jerseys Sale,
    MLB/NBA/NFL/NHL/NCAA Jerseys From China Free Shipping, NHL Jerseys, NBA Jerseys, inexpensive jerseys From factory.

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

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

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

  23. Thanks. Great article

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

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

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

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

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

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

  30. Hi Svetlana,

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

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

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

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

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

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

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

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

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

  39. Excellent post!

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

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

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

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

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

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

  46. THANK YOU!!!

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

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

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

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

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 :)