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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  16. I want to learn look up

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

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

  19. You have a huge mistake in method one. took over a gad damn hour
    you do not put the values as instructed in step 3, you highlight the cells with your input there.....

    2. LOL, true though.

  20. Thanks for your tutorial ... super clear!

    I've made my list, and insisted on only entries that match those in the list ... but my list is long. Can the Excel list automatically limit the visible list as you begin typing ... so if I type "b" ... the list reduced to only those starting with "b", then if I type "o", now the list would only show those starting with "bo". Can it do this?

    Thanks in advance

  21. Hi Svetlana,

    First off thank you for all your advice and effort, its greatly appreciated. I've read through this section as well as some of the links provided herein but have not quite found / understood what I need for my "issue".

    I am trying to create a pay sheet for my staff. We have various staff on different pay grades. We have a separate excel sheet with all our staff names, numbers, paygrade, hourly overtime rate and various other details in rows. e.g. A2=name, B2=number, B3=rate, etc.

    I would like to, using a drop down list, select the staff member by name and then in the subsequent columns have the required information (number, rate, overtime rate) inserted automatically in the subsequent columns in the pay sheet. e.g. in C11 we select the staff member by name. Once selected we require C11, C12, C13 and C14 in that sheet to be filled with the corresponding information of that staff member.

    The rest of the paysheet is fine, it calculates the remuneration with no problems.

    Could you also include the formula for getting the data from a separately saved workbook (we have the staff list stored in the cloud).

    Thanks in advance!

    1. Ivan, a lookup() function can be used in this case. so in C12 type "=lookup(C11,[RangeWithName],[RangeWithNumber])" And then C13 would be "=lookup(C11,A2:A999,B2:B999)". I don't know how many people you have, but you get the idea. The function searches in part 2 for what was referenced in part 1 (C11) and prints what is in part 3. These would change dynamically even if you had the names in a table and the table got sorted often or whatnot.

      It would be possible to reference a seperately saved workbook if instead of referencing that cell, you had the other excel file open and clicked that file instead. You can not move the file or change the name after that, otherwise it won't work!

      You don't need VBA to do this.

    2. I have a similar issue so hope to get your questions answered.
      We have two options in the drop-down list, Cash and Finance.
      If payment is Cash, subsequent columns to show a price less a $399 discount = Total
      If payment is Financed, subsequent columns to show the price as the Total
      I am debating honing my VBA skills for this one.. Yikes! We will appreciate the assistance!

  22. This artical is very good. I learn alot. Can you please describe the OFFSET formula? Means in which scenarios it use.

    1. Hello Dipsundar,

      This is a very good question!

      In Microsoft Excel, the OFFSET function returns a reference to a range consisting of a specified number of rows and columns. You can use it in various scenarios when you need to get a dynamic (i.e. continuously changing) range. You can find more details here:

      The OFFSET function in more detail

      Thanks again for your great question!

  23. Hi. I want to create a drop down list which shows the text for the user to select, but returns an icon based on what's been selected. Can you help? Thx.

    1. Hello Claire,

      You need a special macro for this task. Sorry for not being able to help you with this.

  24. 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 my first value, a blank column of data that i specify will show which i can then enter data into. If I then select the select value from the drop down list, column of data linked to one disappears and allows me to enter responses for value 2 of my drop down list. If i switch back to value 1 from drop down, the data i entered previously should show up. Is this possible? Or would each response have to entered in a different sheet, then an index match formula be used to call up different column responses based on the drop down value?
    Thanks in advance

    1. * If I then select value 2 from the drop down list, ...

        1. Alex,

          I got it, thank you. I've consulted our Excel gurus and regret to tell you that your task cannot be fulfilled using formulas, at least we do not know any way. This can be done using VBA - you need to track changes in "Design change" and update Column D depending on an old / new value. Sorry for not being able to help you with this.

  25. Hi
    i'm looking for a solution. In sheet number 1 i have products name,product part number and description. i'm working in sheet number 2 if i enter part number i want description will be auto generate.
    is any formula is there? Please help me.

    1. Hi Madhu,

      Well, this is not exactly the topic discussed in this article... Still, you can retrieve the description using a simple VLOOKUP formula.

      Suppose you have part numbers and descriptions in sheet 1, in columns B and C, respectively, and row 2 is the first row with data. Then if you enter a part number in cell A2 on sheet 2, and copy the below formula in cell B2, you will get the corresponding description in B2:
      =VLOOKUP(A2,Sheet1!$B$2:$C$100,2,FALSE)

      Please check out our VLOOKUP tutorial for more information.

      1. i want to know how can i make my drop down box in excel remain forever when i open next time? please assist me

      2. Hi

        Great website! But I'm really a novice with basic excel knowledge. I'm looking for a way to have a dropdown list with a range of numbers and then have it give me a number for that range.

        example:

        Price Fee
        $0-$499 = $115
        $500-$999 = $125
        $1000-$2999 = $180

        and so on. I would like a column to have a drop down list with the price range and another column with the fee associated with the range.

        1. Gabriel,

          The drop-down will follow the previous setup for a "static" drop-down, and you should provide the option(s) in the source as follows:

          $0-$499, $500-$999, $1000-$2999

          In the cell you want the fee associated with the range, an IF statement should work just fine (a bit rudimentary, but for what you want it to do, it should work well and not have any real issues). Use this:

          =IF(C3="$0-$499",115,IF(C3="$500-$999",125,IF(C3="$1000-$2999",180,"")))

          Replace C3 with whatever cell your drop-down selection is, then format the column to your liking (most likely as a currency).

          The IF statement basically checks which drop-down is selected, then assigns a numerical value based on the selection. Hope this helps

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)