Comments on: How to make Excel drop down list with multiple selections

By default, Excel data validation only allows users to select one item from a predefined list of options. But fear not, as there is a solution. By using VBA, you can create drop-down lists with multiple selections. Continue reading →

Comments page 2. Total comments: 552

  1. For Multi-select drop-down for specific columns, can I add more than 3 column, what the code

  2. I love the code and it works flawlessly...

    When I open it in the desktop version. When i try to use it online the code doesn't work.

    1. Hi! This question has been asked many times before, as you can see from the comments below. Excel Online does not support Visual Basic for Applications (VBA) or VBA macros.

  3. When trying to use your code Multi-selection dropdown with item removal, I receive an error when trying to select my first item from the list. It says, "Compile error: Can't find project or library." The code without item removal works fine within the same sheet.

    1. Hello David!
      Carefully copy the code, following all instructions above. You can also download sample file, the link to which is at the end of the article. All codes in this file work correctly.

  4. Industrial Engineer here. Thank you so very much for sharing your expertise!! You are appreciated! I tried this with the removal option and had no issues. For anyone trying, you need to save the spreadsheet then close out of excel completely. Open the sheet again and your changes will now be active!

  5. Thank you so much for this!

    Just curious, though - when I select only one item from the list and try to move on to the next cell (e.g., when I press Tab or Enter), it will automatically go back to the original cell, where I had only selected one item. This is resolved by clicking either tab or enter a second time, but I was wondering if there is another work-around for this? I am using the "Multi-selection dropdown with item removal" version of this code and have opted to use ";" as a custom delimiter.

    Thanks!

    1. Hello Kevin!
      Unfortunately I was not able to reproduce your problem. The macro proposed in the article doesn't change anything about how dropdown list works. It only allows you to select multiple values from drop-down list if necessary.

  6. OMG, I finally managed!! Thank you so much, this is GOLD! This is the only way I was actually able to get the code done and to work. Thank you SO MUCH for explaining this as simply put as it code can be :)

  7. Hello, i used your VBA code for multiple selections and removal. The removal isn't working. when i click on an item ive already added it doesnt do anything. Can you help me with this?

  8. Hello,
    Thanks a lot for this post, it has been of great help as I was struggling to have this macro allowing to select multiple values from a drop-down list, to work for only one column.
    Thanks again !

  9. I have 2 multi choice selections on my sheet, I have set up my data validation for one, column I, to be dependent on the other, column H, using the XLOOKUP function. When I have one item selected in column H this formula works, and I am able to select multiple items from the specified range of whatever I put into H. However, as soon as I pick more than one thing in column H, I can't pick anything in column I. The drop down button appears, but I cannot click it to make a selection. Is there a way for me to pick multiple items for column H and get a list in I of just the ranges the correspond to the items in H?

  10. Hi! Thanks so much for this, it's so helpful. I now need to filter the data by some of the items on the dropdown. Is there any way to make the filter such that any I can see all the rows that selected one category, even if that row has multiple selected? So for example, the drop down list contains A, B, and C, and I want to filter for A. Right now, I can filter by "A" or "A and B" or "A, B, and C" separately. However, I want to filter for all that contain A including rows that contain A and B, etc. Does that make sense?

  11. Hi
    I want an option to enter text manually if user select "other" option in drop down How to do that?

  12. Hello,

    I was wondering if I would be able to use the data from the multi-selected dropdowns in charts and graphs? Is there a way to implement this?

    1. Hello Monica!
      Excel drop down list with multiple selections creates a text string of multiple elements in a cell. You can use it in charts and graphs as a normal text value.

  13. I got the macros working, but every time I close and reopen the sheet, the function stops working. Is this to do with my Macro Security settings?

  14. Hi,

    I'm curious if once you have this setup in your sheet, which I do and it is working fabulously, I have another dependent drop-down list that I would like to pull based on the multiple selections in the original cell. Note, I used your additional code to limit the multi-selection to one single cell in my sheet.

    So say in my multi-selection cell I have a user choose the options, "Apple," and, "Bread"
    In the second dependent drop-down, using the indirect function I assume, I would like to then see the full list of apples and the full list of breads in the one single drop-down.

    Is this possible?

    1. Hello Jeremy! This has been discussed several times in the comments below. The standard functions of the drop down list do not allow you to do this. It is only possible using additional VBA code.

  15. Does this address if you have your data validation source list on one tab and the drop-down on another? I may have missed that detail in the instructions. Thanks!

  16. Hello Alexander,

    The code works great thank you! However, it seems that I am the only one able to use multiple selections within the drop down menu, while my team members are not able to. They, and I are not prompted with any errors when doing so. Any insight here?

    Kind Regards,

      1. Hey Alex!

        We are using a spreadsheet which is collaborated on through OneDrive. The workbook is macro-enabled, and the code works perfectly for me, but not teammates.

        Thank you for the help on this.

        1. Hi! When you open a file from OneDrive in the desktop version, keep in mind that Microsoft has a default setting that blocks macros from the Internet. You may want to add the file to a trusted location or make sure it is not marked as downloaded from the internet.

          1. Has a workaround been found to solve this issue yet? I am also having the same issue when using the Excel file as a SharePoint.

  17. Hi! Thank you for this article, it's been very helpful. I used the Multi-selection dropdown with item removal VBA and I am wondering if there's a way to allow custom text as well? I kept getting an error message when I tried to add text after using the drop down list, so I removed the option for an error message in Data Validation, which now let's me add text but it duplicates everything within the cell.

    For example, say my list is Apples, Carrots, and Oranges, and I want to add "(green)" after Apples to only one specific cell, this appears:
    "Apples (green)
    Carrots
    Oranges

    Apples (green)
    Carrots
    Oranges"

    If I try to delete the duplicate, it just adds another. Hope this makes sense! Thanks in advance.

    1. Hello Laura!
      The drop down list is used to validate the data you write in a cell. In a drop down list with multiple selections, you cannot change the text string that you have created. I recommend that you add all the variations you want in the data source for the drop down list.

  18. I used these instructions to create a wonderful multi-select drop-down. I am now having trouble with finding the correct formula to be able to tally all of the selections. When using a =CountIF range and selecting one option that shows within the list - it only counts those that has ONLY that option in the list. Is there a formula that will count each instance of the item, even if it occurs with other items within the same cell?

    2. OR - should I be using a different way to count things? (Pivot table?

      For example, Suzy likes apples, oranges, pears; John likes apples, pears - I want to know in a quick glance - 2 people like apples, 2 people like pears, and one person likes oranges.

  19. Hi, I am using the 'Multi-selection dropdown with item removal' VBA and it works wonderful. But I was using this on a Protected sheet, So I even added the VBA from 'How to enable multi-selection functionality in protected sheet' part. It was throwing an error saying, "Ambiguous name detected" and was highlighting - Private Sub Worksheet_SelectionChange(ByVal Target As Range) part of the code. Can you please guide me on this. Thank you.

    1. Hi! I can't see your VBA code and therefore can't help. I assume that you have duplicated part of the code. Try removing all the code and re-inserting it according to the instructions provided in the article.

  20. Hi,
    Thank you for the great guide. Is there anyway to force the drop down selection to stick to an alphabetical order?
    Kind regards,
    Sarah

  21. Hello, I used the macro for multiple selection and separating each item on its own line. Thank you for the guidance. I am using Excel on Mac and have enabled macros, then formatted my sheet into a table. I'm not receiving a run time error of 502990, Enable events of object application failed. When I run the debug its highlighting this part of the VBA code : Application.EnableEvents = True

    of note, my lists arent all being referenced through INDIRECT rather than just the source of the different sheet in the same workbook. Inputs for the lists are text but not formatted as anything particular.

    Any insights you can impart would be appreciated, thank you!

    1. Hello Nancy!
      VBA code written for Windows may not work correctly on Mac due to differences in the operating systems.
      Many features and libraries that are available on Windows are not supported on macOS.

  22. Hi, I'm trying to create a drop-down list with multiple selections in Excel Online. I tried VBA code doesn't work online. Is there another way to achieve this?

  23. Thanks Alexander for this fantastic tutorial -- it worked perfectly and solved a months-long problem for me. I'm just wondering if there's any way to get Excel to present filter options based on any one item in a drop-down. For example, if cell D13 has multiple items selected ("Apples, Oranges, Pears, Plums, Goose Egg"), is there a way to set up a filter on that column that will show any cell with "Goose Egg"? By default, the filter doesn't seem to care about delimiters, so each list is a new option in the filter options. In other words, I need to scroll through the filter list and check off anything that has "Apples" anywhere in the string in order to see only the cells that contain "Apples". Is there a way to make it so that each list item ("Apples", "Oranges", "Pears", "Plums", "Goose Egg") is a separate item in the filter, or would I need to abandon the multi-item dropdown in order to do that?

    Thanks!

    1. Hello Adrian!
      As I wrote in the comments below, a dropdown list creates a text string. Excel filter can automatically use only this entire string, not its individual elements.

  24. Thank you so much for this code.

    It works perfectly for me if the cells aren't formatted as a table. When I change the formatting to a table and select more than one item I get an error which says "The value in this cell is invalid or missing. Click on 'Display Type Information' for more details".

    When I click on 'Display Type Information' it comes up with the following:

    "Data Type: List
    Restriction: Value must match one of the listed items.

    Blanks will be ignored and are automatically valid."

    I get this error even if the only options in my dropdown list are Yes and No. I tried this as I thought it might be a problem with the length of the options I had originally.

    Are you able to assist?

    1. File -> Options -> Formulas -> Error checking rules -> Unselect "Data entered in a table is invalid" -> OK

  25. Hi Alexander,
    Your step-by-step tutorial above is excellent! The "Multi-selection dropdown with item removal" code works perfectly.

    Is there VBA code or a way to automatically sort the list items in each cell alphabetically once they are added? I have the list items on separate lines in each cell with a carriage return between them for readability.

    For example, if my multi-select list contains:
    Apple
    Banana
    Carrot

    And I select "Carrot" first followed by "Apple", the list currently will show as:
    Carrot
    Apple

    It would be helpful if the multi-select items were forced to display alphabetically as:
    Apple
    Carrot

    We have 73 items in our list and the list itself is sorted but we don't always select the items alphabetically when adding.

    Thank you!

    1. Hi Misho!
      Earlier in the comments below, I mentioned that to sort words in a text string, you need to create a separate VBA macro.

      1. Thank you, Alexander. It worked perfectly.

  26. I have copied your code into my workbook for the Selections on separate lines tab. Unfortunately, my dropdown will still only allow for single selection of items. I thought I was doing something wrong on my own sheet, so I downloaded your sample file to play with it and see exactly how it is working. Unfortunately, when I downloaded that file and started to play with making selection modifications on the "Selections in separate lines" tab, it was no longer allowing me to multiple select any items. For example, I went to the Salad ingredients drop down to unselect "carrot". Now the only item actually showing is "carrot". Is this a versioning issue? Is there something else I can do to fix this?

  27. Hi, Thanks for the code.
    I am getting the error message " run time error 424" "Object required". Running the debugger then highlights line 12 If Destination.Count > 1 Then. What am I doing wrong? Thanks

    1. Hi! Not enough data to give a concrete answer. I don't know which macro you are using. But in none of the macros is this code written in line 12. Check your code.
      In this code, the Destination object is a cell that changes on the worksheet, and has text or a number written in it.
      If Destination references an object that does not have a Count property (such as a dialog box), you will get this error.

  28. Can you add to the Dropdown with item removal code to create a dependent multi-select dropdown in another column? For instance, I choose 2 properties in one column and that filters the options available to select in another column, Areas of Use?

    1. Hi! I have already answered this question several times in the comments. You can't use Excel's standard tools to create a dependent dropdown list that uses the two values selected in the multiple choice list. You can split those values into separate cells and then create a new list of values for them using the FILTER function. You can use these instructions: How to make a dynamic dependent dropdown list in Excel an easy way.
      Or you can create a dependent drop-down list for multiple values using VBA.

  29. What if the table is on another sheet how to structure the indirect command for it ?

  30. Hi, thanks for the info.

    This was working, but I had to save it to a SharePoint site for other team members to access. Is there a way to make this work?

    VBA Code I am using is below (I am trying to apply this to column AM (39) with the items selected on separate lines.

  31. I've added the use multifunction with item removal with the updated password option that is posted in the comments. I save my worksheet with allow edit ranges under the review tab, then select the sort and use autofilter boxes before entering my password. The multiselect works perfectly with no error messages but it doesn't allow for me to sort anymore. When I go back in to unlock, the autofilter and sort options have been deselected. How do I fix this?

    Thank you!

    1. Hello Loretta!
      Unfortunately, I can't see your workbook and your data. However, you can only select and add multiple values to the text string in a cell using the Multiple Selection drop-down list. It does not have any effect on your ability to sort your data. However, you will not be able to sort the data by a single word from that text string.

  32. Alexander, First: you are a superstar. Second: Is there any way to parse the final contents of the multi-select list such that I can split them into individual items for a =FILTER function? By way of example, lets say my validation list={Apples, Bananas, Oranges, Pineapples} (obviously with this being a list in multiple rows of a single column in excel set as the data validation source). If I put the final result of the data validation list in F1, would there be any way to use =CHOOSECOLS(TEXTSPLIT(MyEvalCell, ","), 2) to get the second item like this =FILTER(A1:A200,(B1:B200=(choosecols(textsplit(F1,","),1))+((B1:B200=(choosecols(textsplit(F1,","),2))) etc?

  33. This guide really helps me a lot.
    Really Appreciate the effort.

    I have a question to ask.
    I want to add color-coded dropdown system on top of code under "Multi-selection dropdown with item removal" area, to highlight elements already been selected.
    What I want to do is color listed elements within the dropdown menu, not the cell itself.
    But all guide I search through online is about color coding the cells. I cant seem to find a case that relating to this topic.
    Is it possible to add color or highlight to elements in dropdown menu?

  34. Alexander,

    This VBA works great, I appreciate the write up. I am using the "new line multi selection" with the vrbCrLf function and it is working within the cells. However, the cell only displays the first selection made from the list. I have to manually click each cell to show the items displayed. I have the rows formatted to Autofit Row Height etc. I am not sure what is stopping the cells from displaying all the contents selected.

    Could you provide me with some insight on what might be working against me?

    Regards,

    Kenyon

  35. I got the drop down to do multi-select (wonderful) but when I execute the "Advanced" filter to actually extract the data to another sheet, it comes back with no data. Selecting one item and it appears fine.

    My program works by having the user selection in its own tab and when "Get Data" macro invoked, it copies it and transposes to the 2nd line (beneath the headers) of the output sheet - and two further lines down is the "Output" range. All very basic and nothing out of the ordinary - and, as I mentioned, works just great with one item per selection.

    Add a 2nd selection though, it comes back with nothing at all. Can't work it out at all.

    Help! (Please)

    1. Hello Mark!
      The multiple select macro uses a drop down list to create a text string that consists of multiple list items. I have no way of knowing how your macro and advanced filter work with this text string.

  36. Thank you for the amazing VBA! Is it possible to allow manual text entry that doesn't contort the multi-select entries? When I add a manual text entry after using the drop down selection, it messes up the drop down selections.

  37. Hi, this worked great. Is there a way to only have it limited to one column? There is one column of information I need to be multiple select and my other columns that have drop down lists need to be single choice items. How would the VBA script change? I've used AI repeatedly to work through this and cannot come up with a solution that doesn't give me an error.

    1. Hello Emily!
      If you had read the article carefully, you would have found the answer in this section: Multi-select dropdown for specific columns, rows, cells and ranges.

  38. Is there any way to copy and paste the worksheet into another worksheet without losing the VBA code? Or do I need to reset it each time?

    1. Hi! Unfortunately, there isn't a direct way to copy and paste a worksheet with its VBA code intact to another worksheet within the same workbook.
      VBA code is tied to the specific worksheet module, and copying the worksheet itself doesn't automatically transfer the associated code.
      However, there are a few workarounds you can consider:
      1. Exporting the VBA Code:
      Open the source workbook and press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
      Insert a new module by clicking Insert > Module.
      Copy the VBA code from the source worksheet's module.
      Paste the VBA code into the new module in the destination workbook.
      2.Copy the worksheet to a new workbook.
      The VBA code should be transferred along with the worksheet.

  39. Hey, This has been working great thank you. I have one issue that I cant figure out though. There seems to be a max on the number of characters that can be added to a cell when selecting multiple drop downs. When this happens it results in the cell turning into ############. The list I am using is a series of sentences so each item has a large number of characters. Is there a go around?

  40. How do you get this to work for online excel? OR is it not possible?

  41. Everything works, but the multi-select no longer works once I save the document! What am I doing wrong? I'm saying it as a Excel Macro-Enabled Workbook (*.xlsm). Please help! Thank you!

  42. Hello, is there a way we can highlight selected items from the dropdown list to avoid getting it removed the second time it's selected?

      1. Oh, I mean we select items from the list right? For example, a list of fruits: Apple, Banana, Mango, Lychee, Orange. I am selecting Apple, Banana, and Orange only from the dropdown. Is there anyway these words can get highlighted from the dropdown just to show that they are already selected/added to the list?

  43. This helpful by far dude, I couldn't help to say it out loud... but how to apply it in every single sheet inside of a workbook?
    Should I apply it over every sheet making use of Alt + F11 and double clicking on the desired sheet and adding the code again and again?
    That's kinda exhausting and frustrating in a limited way.
    By the way, +10 and reco for the post mate.

    1. Hi! I wrote earlier in the comments that the Worksheet_Change event in VBA (Visual Basic for Applications) is used to perform certain actions based on changing cell values in a worksheet. However, it only works on the current worksheet. Therefore, the code needs to be installed on each of the worksheets in which it is needed.

  44. This is so helpful! Is there a way to make a dropdown menu that also allows custom text input into a given cell? When I add custom text, the values already in a cell are duplicated.

      1. Thank you

  45. Is there a way to add a count function when allowing duplicates? That is to say, if I selected Apricots 3 times is there a way to have it display Apricots(3)??? I hope this makes sense.

  46. I searched high and low to try work out how to do Multi-selection dropdown with item removal AND How to enable multi-selection functionality in protected sheet. I found the following workaround:

    Forget adding the above code for "How to enable multi-selection functionality in protected sheet" mentioned in the article if you are having issues using it and just add "ActiveSheet.Unprotect Password:="Password" & "ActiveSheet.Protect Password:="Password" in the following places within the code:

    If Destination.Count > 1 Then Exit Sub
    On Error Resume Next
    ActiveSheet.Unprotect Password:="Password"
    Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitError

    AND

    end of the code

    exitError:
    ActiveSheet.Protect Password:="Password"
    Application.EnableEvents = True
    End Sub

    Hope that helps :)

    1. You did it! Thanks a lot!

  47. Alexander,

    This is awesome, having an editable list of items in a data validation field is something my internal customers require. I appreciate both the example workbook and the VB behind it. I have learned a ton.

    However…
     
    I have implemented the "Item removal" version in my workbook and it does what I want. However, if I use that data validation in a cell of my own table I get a Data Validation alert as soon as a second value is chosen.

    Very simple example. Workbook with two sheets. Sheet two contains the table "Years" and consists of a single column "Years". The rows have a single year, such as 2024, second row 2025, and so on. Sheet one contains a two column table. First column is "Thing", second column is "Year(s)". The data validation is List with "=INDIRECT("Years[Years]")" as the Source. One reason I want to use a table is that there can be any number of rows from 49 to over 12,000. But I'd rather not set every row up in advance. Do you have any suggestions?

    1. I found a way to ignore all errors by putting:
      Application.ErrorCheckingOptions.BackgroundChecking = False

      In the workbook open event and
      = True

      in the Before Close event.

      I don't love this because I don't want to be changing global user preferences for them.

  48. Hi, Thankyou for sharing this. I have been using the ‘Multi-selection dropdown with item removal’ code on my worksheet successfully! I am now setting up a Data Entry User Form and was wondering whether I can use this code to have a Combo Box behave in the same way?

      1. Thanks Alexander.
        Are you aware of any ways or able to point me in the right direction as to how I might get this or similar functionality with a user form? Otherwise I am thinking I would need a separate box for each instance to collect the data.
        Regards.

  49. Hello Alex,
    I am really grateful for the information that you have provided, in such a detailed manner. Something that I would also like to know is that is there any code that we can modify/write to limit the number of selections made by the user?

    1. Hi! You can try limiting the number of separators that are used in the results of a dropdown list with multiple selections.

  50. Hello, I am trying to do multi-selections in a protected sheet but I am getting an error (Ambiguous name detected: Worksheet_SelectionChange). I think I may be putting the protected sheet code in the wrong place. Can you assist me in combining the multi selection and protected sheet code? Thanks!

    1. Hello Lauren!
      I am unable to insert the code into your workbook. Carefully follow the instructions given in the article. Also, this issue has been discussed many times in the comments below.

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