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 5. Total comments: 270

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

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

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

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

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

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

  7. THANK YOU!!!

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

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

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

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

  12. interesting and helpful

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

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

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

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

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

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

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

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

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

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

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

  24. Thank you very much

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

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

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

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

  29. It is make some of schedule.

  30. Many Many Thanks

  31. Nice!!

  32. Suppose I have different columns (say A,B,C) with different values for fixed fields (say X,Y,Z). How do I create a drop-down of columns A,B and C such that when I select A the corresponding values of X, Y and Z is displayed?

    1. By far this is one of the most detailed drop down list tutorials.
      For below scenario, can a drop down list be created depending on the first column selection?
      For example
      Column A Column B
      1 Amy Amy
      2 Bob Bob
      3 Bob Bob_wife
      4 Chris Chris

      Provided Bob is selected, a drop list will show only "Bob, Bob_wife"
      Much appreciated for your time.

  33. I have made the drop downs and they work great. However you can still type in them how do I make it so you can only choose from the drop down?

  34. Спасибо,Светлана,за подробную статью. По-русски не публикуетесь, где-нибудь. Буду признателен за ссылочку.

    1. Добрый день, Сергей. Приятно знать, что к нам заглядывают и соотечественники :) У нас пока есть только английская версия сайта, и поэтому и все мои статьи тоже на английском. В перспективе возможно появится и русская версия, и тогда переведем самые популярные статьи.

  35. I would like to know how to use a "clear" button for a dropdown menu. I have a dropdown menu that lists hockey players. But if I want to re-use this tab to select another group of players, it would be awesome if I could click one button to clear all tabs and revert to the top selection word "Forward". When you select a name from the drop down, it populates cells to the left of the drop down. When you scroll to the top of the drop down and click "Forward" or "Defensemen", it defaults the cells to the left back to normal (no name or team or salary number). I'm pretty sure I'll have to create a button and use a macro but not super knowledgeable on how to do this. Your help would be greatly appreciated.

    Thanks very much.

  36. I need to create a drop down list in below format

    In Colom A1 drop down
    1. Student Name
    2. Teacher Name

    1.if select colom A1 "Student Name" then colom B1: need to show 3 category like " School fee need to enter" or "Transport Fee need to enter" or "Food Fee .

    or

    2. if select colom A1 "Teacher Name" then colom B1: need to show 3 category like " Salary need to Enter" or "Allowance need to enter" .

    Please help me

  37. I need to create a drop down list in below format

    1.if colom A1 "Student Name" then colom B1: need to show 3 category like " School fee need to enter" or "Transport Fee need to enter" or "Food Fee .

    or

    2. if colom A1 "Teacher Name" then colom B1: need to show 3 category like " Salary need to Enter" or "Allowance need to enter" .

    Please help me

  38. I need to create a drop down list in below format
    if colom A1 "Student Name" then colom B1: need to show 3 category like " School fee" or "Transport Fee" or "Food Fee . Please help me

  39. Hello,

    First of all, thank you very much for this article.
    My Question is : Can we give default name to cell instead of showing in via input message. eg. Select from Dropdown.

  40. Hi

    how to insert a check box as an option in a drop down list. Thanks in advance

  41. Iam working in construction company make labour wages in excel we want to know formula how can we feed information like website can generate information by entering just personal number please send formula link Svetlana Cheusheva

  42. Hi Svetlana,
    I am using Excel 2007, but am not experienced. I have set up an Invoice Template on worksheet no.1, and have keyed client information (names and addresses) under worksheet no.2. However I would like to be able to type in a clients name in the Invoice billing area and have a dropdown list which shows my clients names and addresses from worksheet 2 and then click on a name and the invoice automatically prefils with this information. I just don't know where to start. I have tried looking everywhere for information on how to do this which is easy to understand, but have not had any luck

    Please can you help me?

    Many thanks, Michele F

  43. Hi,

    I am looking at having a simple "drop down box" with items to select (i know how to do this) and then if I made another "drop in box" and used the same list again, I want the "item" chosen in the first "Drop Down box" unable to be selected.

    thanks,

  44. Does not make any sense - these tutorials should be easy for everyone

  45. Really helpful. I think now i dont need to search for any other website for excel help. Thanks

  46. Hi

    I want to have a drop down list of say Towns which contains data for each town, say like population, number of bars, etc

    When i click on a town the data changes

    How is this done?

  47. Hay there,

    i gotta a problem....
    actually i wanna find out the name easily from drop down list by type first letter of that word by which it will be appeared the words those started with the letter i typed. kindly suggest me.

  48. Hi Svetlana,

    I am looking for the filter in excel like if we type any character which are in the dropdown list, that should auto populate in the below of the cell.

    Like if you find any city in http://www.cleartrip.com. When you enter the 1st letter "D" to search for "New Delhi". It auto populates all the entries which are starts with "D".

    If you could give me your mail id. I can send you the screenshot for better understanding.

    Please help me with the solution.

  49. Hi svetlana,
    I appreciate you by given quick response to every user.Please help regarding dropdown list with some parameters(Dynamic chart),as i created successfully the list but parameters value also display on the graph but limits not automatically updated with the each parameters. Thanks in advance for your support.

  50. Hi svetlana,
    I'm trying to create a drop down list in a form of states.
    I'd like it to be like an online form where as you type the letters in the box it automatically takes you to that state example: As I type o 3 times it takes me to oregon, the third state beginning with o.
    Can that be done in excel or is that an html thing that only works online?
    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 :)