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

  1. Hi,

    I am just wondering if its possible to display the contents of the drop down list which is quite similar to the content I am entering to the cell, for example:
    I am typing ERM1500-RPH which is one of the content of my data validation. and there are actually few more data which are quite similar to this. ex: ERM1700-RPH, ERM1800-RPH and etc. I am just wondering if I start to type "ERM" in the cell, is it possible that all the options that starts with "ERM" will appear in the drop down list options.(I hope someone can imagine what I'm trying to say.) lol...

    Please help me with this. Thanks.

    -shey-

  2. Hi, I have this problem that keeps me entering duplicate Document Code, How can I prevent this? Data Validation is not functioning I use

    = COUNTIF(A:A,A1)=1

    Example

    NT CP-PROC-xxx
    *the code starts here
    NT CP-PROC-001
    NT CP-PROC-002
    *and so on

    The cells are merged into 12

    pls help.

    1. I mean document code starts here

  3. Thank you for a very clear explanation about drop down...thank you thank you thank you....

  4. Is it possible to create a drop down list in footer or headers? Thank You

  5. Thank You

    Great Lesson

  6. Great help!!! very useful

  7. hai i want to know about that dropdown list so that it wont pick a name twice. example analyst comes twice in drop down list it should not come please tell

    thanks

  8. Hello, Svetlana. The article is very good. I did drop down (answers to the questions) but now I don't know how I can calculate the number for each answer (ex: for "Yes" 10 poins, for "No" 5 poins etc.
    Now I can't connect the answer cell and another cell in which I'd like to the the amount of points.
    Thank you in advance for your answer.

  9. thank you for this information on this website - wondering when creating a list, where do I go to allow the user to be able to check more than one option from the drop down.

  10. I can't not drop down list next two sheet, so how can I do it?

  11. Hello
    I would like to add a message when a value of a drop down list is selected
    is this possible.
    Using MO 2013

  12. This is very helpful.

  13. Hi,

    Great article.

    I am looking to create a drop down list as described. However using this example I want to be able to pick ingredients from a list of ingredient names in the drop down box (flour, eggs etc). On doing so I want it to represent a price to be used in another formula. So for example if flour cost £1 I want to select the word flour but for it to input £1 in the cell.

    Please help.

  14. In search box not a cell

  15. drop doun list in side of box like

  16. thank you

  17. thumps up. GREAT!

  18. Thank you very much for the postings. I have always leant alot from your work. However am wondering whether there is some source of data you can provide on which we can practice on these steps provided.I am still a student and have not have much access to data on which to practice on. I would be more than greatful to be provided with something. Thanks again.

  19. Hello,

    I have a large data set for the drop down menu is there anyway to type the name of the specific data I'm looking for instead of trying to scroll down to find it?

    Thank you

  20. Hi,

    I have the problem that the drop-down list does not work after I sent the excel spreadsheet as a template to my co-workers in order for them to enter their data.

    What can be the reason for this behaviour and how do I fix this?

    Thanks.

  21. Hi all

    is it possible to make drop down list with content which will be depended on the input of another drop down list? for example I have two drop down lists, in the 1st list I have "Cars" and "Phones". If I will choose "Cars" in the 1st list, I want in the 2nd list to appear only "BMW", "Toyota", "Mazda"; if I will select in the 1st list "Phones", I want in the 2nd list to appear only "Samsung", "Nokia", Motorola".
    Can anybody help me on this issue?

    Thank you in advance.

  22. hello
    how can i make a drop down list by default show "--SELECT--" when user open the excel file?

  23. Thanks This has really helped me with my work!

  24. hello
    i want to know is it possible for a drop down list to filter data by pressing keys for example i want to see all the word starting from A when i open list and press A and similarly for other alphabets

  25. hello
    thank you for your insight. I am attempting to create a drop down menu that is color coded. How do I do this? I not able to find the instructions. My lists are simply words without the coloring.
    thanks.

  26. Hi,

    I have a query as below:
    I'm trying to create a pricing spreadsheet that has headings like "Course group", "Course name", number of users, Price etc. Under the heading "course group", I created a drop down list of all the "course groups" that I have like accounting, asset management etc. Now under the next heading "Course Name", I want to get a specific set of options (course names) in the drop down when I select Accounting for example under the heading "Course Group". How can this be done?

    Thanks

  27. thank you somuch for this tutorial, this is very helful for me because I am a documents controller, I really need this, thank you...!

  28. Creating a drop down list is new to me but after reading articles, it appears fairly easy to create. My question is how do I make that drop down list automatically appear in every cell in the column to infinity? I believe that there are not only issues with using control C and control V, but also that only copies the cells selected and my worksheet will grow. In addition, does inserting a row work? Or is it better enter the information on the last row and then sort the data?

  29. I am creating a spreadsheet with multiple columns of drop down lists. The user will sometimes copy and paste their data into the spreadsheet, so I don't want a warning box to come up for every cell of data that doesn't match what is in the drop down. Can I just turn that data red instead of popping up a message? Then, the user can quickly scan for the red data and determine if the data needs to be changed or not. Thank you for your help.

  30. Hi,

    i have 2 drop down list in excel. in first drop down list value is 1 to 5 and second list A to E. give me solution that if i select "A" in drop down list than value in first drop down list will show "1" and so on for other.

    Thanks.

  31. Hi,

    I am using the drop down list for a time sheet style workbook where a user continuously enters time information and selects a job from a drop down list, which is then totalled up on a summary tab.

    The problem I have is some jobs change names at some point, so I need to change the name in the list. The name is changed in the drop down list, however all previously entries remain as they were when they were entered (the old job name).

    Is there a way to dynamically update the selections of previous entries?

    Thanks.

  32. I have three drop down list use the name range
    when the first list is category and the others two list depend to show what they contains on the first what, and I realize that I misspelled some category and I correct the name range but the existing cells didn't update
    how I can fix that ?

  33. thank you for this information on this website - wondering when creating a list, where do I go to allow the user to be able to check more than one option from the dropdown?

  34. Create a dynamic (automatically updated) Excel dropdown does not work

  35. Hi,
    I am trying to create a dynamic drop-down list. There are multiple tabs in the workbook - in the first worksheet, users select or enter in information into a column. In the second sheet, I want a drop-down list containing the entries from that column. As users add more entries into the first sheet, the drop-down list on the second sheet needs to reflect that. I tried to create a dynamic drop-down list as described, and it does show additional entries as they are entered, but it also includes all of the blanks from the column on the first sheet, making it a very long list of blanks. How do I fix this and make the drop-down list only contain the information entered by the user?

    Thanks!

  36. Hi,

    I was wondering if I could create a list which will contain codes and have their respective descriptions but only show the codes when selected.

    Please lemme know if its possible.

    Thanks,

    John

  37. Hi,

    I have created a several combo boxes (form control) in my spreadsheet and in the format control, set the input range by selecting a range of cells from Sheet 2 of my document. I have several combo boxes, connected to several different lists. My problem is that if I select the first option from any of the dropdown lists, they do not save. The only way I have found to get around it is to put a blank field at the top of the dropdown list - but not ideal as I don't want people to be able to select the blank option. I could use data validation, but I prefer the look of combo boxes rather than data validation boxes.

    Any ideas ?

    Thanks
    Cathy

  38. Hello,
    I am preparing an excel sheet and want to create a set of tabs let say maximum 10 and for a project reference I need to show only 6 tabs instead of 10 , all these tabs have the same informations.
    if we need 4 i have to do something to let my workbook show only 4 tabs, next project i need to show 7 so i have to show only 7.
    please could you help on this?

  39. Hello Svetlana, Awesome article and much appreciate the responses that you have provided to various users like me.
    My situation seems trivial but not addressed in the responses above
    I need to create a picklist from a bunch of data residing in multiple columns in multiple sheets. To explain , I have data in 1st worksheet column A1:A10 , D1:D10..then in worksheet 2 Column A1:A10 , C1:C8. I want the picklist to be created in worsheet 3,column H , merging the data for all the 4 column ranges mentioned above. Is that possible ? Thanks much in advance.

  40. This was great information. I use lists in Data Validation all the time. I'm creating a new Price List that has 3 basic pricelists (Named Range). All customers are based off of 1 of these lists then calculated with specific discounts tailored to each customer. I'd like to use a drop-down menu to choose 1 of the basic lists as a base for that customers calculations. I can make a simple formula using only 1 named range, but I'd like the formula to see the drop-down menu choice and use the correct named range. Is this possible? Any help would be greatly appreciated.

  41. Thank you very much, Its always useful for beginners

  42. Hi Svetlana,
    This excel drop down article is awesome & very useful.
    I'm using it to create my inventory stock list.
    But how to further sorting my dynamic stock list to more details like below:-

    1st Option: Brand
    2nd Option: Item - Dependent on Brand
    3rd Option: Model - Dependent on Brand & Item
    4th Option: Serial Number - Dependent on Brand,Item & Model.

  43. I have created a document with the drop down lists. The data is regarding enquiries and members leaving our facility. It is organised by date range, occasionally we have to insert more rows within the data. When I insert a row it is losing my ability to use the drop down lists. Is there a way around this? Many thanks

  44. i learn so much with your article

  45. Yes!!! got the information how to edit and add more names if required.

  46. My xls have 3 drop down colums.Now does that causes the xls to store lesser than 65536 rows.Beacause my xls is able to store 21845 rows which means 65536/3=21845.
    On opening the xls having more than 21845 gives error "File is corrupt".
    Please help with the issue??

  47. Hai,
    I'm seeking a solutions for the below mentioned task. Please help me.
    I have two xl file called 1 and 2. In this two xl files 1 and 2 include separate working sheet called R1 and R2. In Sheet R2 so many values in A columns (say A0 to A100 - All the 100 cells having values, that means no cells are blank).
    Now I want to link each value from Sheet R2 (A0 to A100) to R1's B column.
    The problem is that while linking I want to get the result like this. A0 to B0, A1 to B3, A2 to B4, A3 to B6 etc.
    Regards,
    Jijesh

  48. Hi Svetlana,
    Hope you can help me out with the below issue in excel!
    I need to enter the values for a dropdown list in a webpage automatically, once clicking the Command button in excel sheet.
    For each and every replacement in the dropdown list, the values related to that need to be exported as a excel file.
    for further understanding the webpage will look like (https://drive.google.com/file/d/0BwyKq2OBVvcFT3VGWUZvSXRiRE0/view?usp=sharing) please save the file and open with a browser for the GUI.
    I'm new to excel VBA & Macro, so i cannot make the data in excel sheet to place it in the dropdown list in that webpage.
    Please let me know for further clarification, also if you've any ideas please let me know.
    Thanks in advance !
    Regards/ Pravin

  49. Thanks Svetlana.

    Saving in the XLSX format done the trick.

    Cheers :)

  50. Hi there. I am making spreadsheet for payroll using the method " Creating an Excel drop-down list based on a named range ". I fulfilled all steps yesterday creating a template however going back today it seems to not have saved. I receive a message when saving saying

    " one or more cells contain data validation rules which refer to values on other worksheets. These data validation rules will not be saved "

    Any help would be appreciated :)

    1. Hi James,

      This seems to be a known issue if you are working with .xls workbook in a new Excel. If this is the case, please try the following suggestions:

      The warning is incorrect, there is no loss of functionality. You can save the file without losing the Data Validation. You can also avoid this message by one of the following workarounds:

      1. Save the file in the XLSX format.

      2. Uncheck the "Check compatibility when saving this workbook" option.

      For more info about this issue, pleasr see:
      https://support.microsoft.com/kb/2757267?wa=wsignin1.0

      1. Hi Svetlana,

        My workbook uses macros, which the XLSX format does not support. If I chose macro-enabled format then the dynamic data validation method does not work. Looks like i have to choose between the 2.

        Any suggestions?

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