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

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

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

  3. Thank you very much

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

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

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

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

  8. It is make some of schedule.

  9. Many Many Thanks

  10. Nice!!

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

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

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

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

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

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

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

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

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

  19. Hi

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

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

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

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

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

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

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

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

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

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

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

  30. Thank you so much...

  31. I want to learn look up

  32. My values in the drop-down menu don't match the values that appear in my worksheet. How do I make adjustments so I get what I expect? Thanks.

  33. I'm developing a testing platform. I'm struggling to come up with a formula in Excel for the following tasks

    Add Pass/Fail/Advisory dropdown list to the spreadsheet with some functionalities.

    When Fail/Advisory selected from the dropdown list. The user have to have a few lines with the outcome results. All the results then should be linked to a different Excel file (report page) where the outcome can be displayed.

    The report page needs to be capable of being saved independently of any other pages which it is pulling data in from so we can email the report.

    I can't find formula to update Excel sheet with data from different spreadsheets. Could anyone help me out please.

    Regards,
    Will

  34. Hi
    I have 6 sheets in a workbook.. In the 1st sheet(home page) I need to create a Combo box list. The list contains the sheet names 1,2,3,4 & 5. When I select 1 from the combo box it should directly go to the respective sheet 1.

    I don't know how to do this procedure using combo box. Can you help me in this.

    Regards.
    Palani

  35. data Validation is not avalable to select how do i make it avalable?

  36. Thanks
    Good example

  37. Hello, I just moved into a logistics position within my company and I am using sheets that employees that no longer work with the company made. Within one of those workbooks I am having an issue with a drop down box. The way it is set up is by choosing a certain customer's name, it will draw info from mulitple cells and pull that customer's entire address and fill in cells on a diffferent sheet within the workbook. The problem I came across is that the drop down box stopped at a certain row even though there is more rows to be pulled up. I have been able to add these rows, but not the entire data from the row. I keep getting error message stating "The list source must be a delimited list, or a reference to a single row or column."

    The rows (customers name) that was set up prior to my taking the postion works and pulls all info from different columns. The rows that I have added to the drop down box will not pull all info from that cell and I cannot figure out how to do what I need.

    Please assist. Thank you.

  38. Is it possible to make a list in a list like:
    The dropdown will be e.g. a, b, c, d, e. When you stand on e.g. "a" antoher dropdown menu will appear with e.g. add 1, add 2, add 3....

    a
    add 1
    add 2
    add 3
    add 4
    b
    test 1
    test 2
    test 3
    c
    d
    e

  39. Thank you so much for the article- it was very clear! my question is this:

    is there a way to make a drop down table? instead of clicking on a cell and making a list come down, is there a way to make an entire table come down? the cells in the table don't have to be clickable- just drop down.

    thanks again for your amazing article!

    -Sarah

  40. Love this article.

    Is there a way to have the drop down list items disappear as they are selected?

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

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

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

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

  45. Thank You

    Great Lesson

  46. Great help!!! very useful

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

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

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

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

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