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 2. Total comments: 267

  1. I've been using drop-down lists since the beginning of time, so I'm very familiar in setting this up and using it. Search in drop-down made my life so much easier, but recently it just stopped working - across all Excel sheets and workbooks that I open. I did not change anything - it is just not working - even if I use the mouse scroll button the scroll is not working, I have to drag down the little scroll bar in the box. I use Office 365, Windows Professional - I've done updates, restarted my laptop a thousand times, but no luck - does anyone know how I can fix this.

    1. Hi! Specify how you searched in drop down list: with an ActiveX element or with a VBA macro in an ordinary drop down list. If the second option, there is probably a problem with the macro.

  2. hi, pretty page but I do not found my problem...
    I have a dropdown list with 26 entry BUT in use I can see always the first 8 and I have to srcoll down all the times if I wants to use an entry. My Question: can I prolong the presentation-list that I can see more of my list ???
    many thanks and regards from germany

  3. Hi, great site!
    My question is if it is possible to edit the format of the drop-down box.
    It is now 8 rows and i like it to have about 20?

    Regrads,
    Frans

  4. Hello Svetlana,
    Congratulations on your articles and on what you or your loved ones are going through.
    Concerning the paragraph Excel data validation list based on a table, I don't understand why you have to go through a named range. Can't we link directly to Table[[#data],[col_name]]? Maybe by coupling with an Indirect function?
    Sincerely yours and congrat to all your team, the info you give is most valuable
    Al1

    1. Hello!

      You are absolutely right - the combination of INDIRECT and table references works nicely. I've updated the instructions, thank you!

  5. Hello,
    It is a great article, I am from Pakistan when i am making a list like a1,a6,a12,a20 using this range hold the Control Button but this range not accepted in the list and giving a error I also used semi colon(;), but is also not accepted, it is only accepted continuous range like a1:a20 so please help

  6. Very very helpful artical..

    Thank you so much..
    Have all success in your life.

  7. VERY, VERY good & useful explanations. Thank you for taking the time to do this.

  8. Hello Svetlana,

    Thank you for a great tutorial and your through analysis and step by step presentation. You also did a wonderful job to explain the different options and pros and cons of each.

    The only step that did not work for me was when I put the Table Name as the Data Source. The Database is in Sheet1, and the Table1 is in Sheet2 (column A, rows 2:30 with header on row 1).

    Your input will be much appreciated. Thank you.

    Joe

    1. Hi Joe,

      Thank you for your kind words about this article! You are right, the table method does not work in the current Excel version, though I can swear it did work back in 2014 when the tutorial was written.

      Anyway, you can use the following workaround:

      - After creating a table, create a named range for a column of data without including the heading cell. For example, you can define the name "Ingredients" that refers to =Table1[Ingredients].
      - And then, create a data validation list based on the name.

      This extra step may sound like a needless complication, but it does make your drop-down list expandable - when a new item is added to the table, it will appear in the list automatically.

      I will update this part of the tutorial ASAP. Thank you for drawing my attention to this! :)

      1. Hey Pretty..
        I need your little help.. for Data validation can you help me...

  9. hello svetlana. Is this article the Italian translation of your article?

    https:// excelacademy.it/6623/creare-un-elenco-discesa-excel/

    1. Hi Luke,

      I do not speak Italian, but the contents seem very much alike :)

  10. Another great article, thank you so much!

    I've been using data validation based on table columns as my standard method since quite a while. However, there's one thing that annoys me: You have to create a separate named range for the table column (as you cannot put the table column reference as source into the data validation directly). Let's say I call the named range "Dropdown" and it refers to "Table[Column]" and has workbook scope.

    Now, when I copy any sheet within the workbook a new instance of Dropdown is created automatically that has the new sheet as scope. I didn't find any way to avoid that. It even happens when I copy a sheet that does not use Dropdown at all.

    Do you know how that can be avoided?

    1. I have this problem to. A bug since I can delete those duplicate named ranges that have a worksheet scope and the validation list will still work since it's using the workbook scoped original named range.

  11. Thank you very much sir, for the simplest explanation on how to edit items on the list. It made me reduce my stress for editing a form needed ASAP. Thank you very much.

  12. Drop down list is not appearing on selection of arrow from begining.

  13. Hi. Please have a look at the first "drop-down via comma-separated" option. You mention in step 3 to use a comma (,), however this should be a semi-colon (;).
    Otherwise thanks for the great help.

    1. Hello Jeanette!
      The use of a semicolon or semicolon in formulas is determined by the regional settings of your Windows.

  14. Great explanation. It solved my query in minutes. Thanks a lot!

  15. Thank you for this very useful post! I was searching in the internet about providing a table as a source for the drop-down list. It solved my problem.

  16. Dynamic drop down does not work, as prescribed.

  17. I created a drop down list for expense type and created vlookup for tax rate based on expense type. However if user picks any expense type and then delete the entry then it gives an error message of NA in tax amount column. How can it be taken care of?

  18. I have a query.
    I have already populated some values in the drop down box.But now I want to insert a value in between the list of drop down values.
    example,
    I have suppose list of values 1,2,3,4....in my drop down list.
    but now I want 1,2,6,7,3,4....(It means that I have to Insert two new values in between)how to do this? I have analysed all the ideas . Could anyone help in this?

  19. Text size for drop down list is coming small. Is there a way to change the text size of the drop down list items.

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

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

  22. Good stuff!

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

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

  25. thank you very much for your suport

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

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

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

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

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

  31. Hello,

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

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

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

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

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

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

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

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

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

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

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

  42. Thanks. Great article

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

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

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

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

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

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

  49. Hi Svetlana,

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

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

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