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

  1. Hi Alexander
    Thanks for your comprehensive useful post. Is there any way to allow multi-select from a dropdown list on excel online? For example, if I wanted to select more than one employee for a project.

    Thanks in advance!

    1. Hi! Excel Online does not support VBA. Therefore, no macros or custom functions work there. We have already written about it in the comments above.

  2. I have copied the code exactly as posted with no success.

    I then downloaded and opened the example document and enabled macros, even that file doesnt do it. Is there something I am missing or maybe a further excel setting that is disabling macros?

  3. This was super helpful and instructed me how to do exactly what I wanted. However, I am now running into a new issue. I want to count the number of unique occurrences for all selections made from the multi-selection list. Whenever I use a count function, it does not count any occurrences in cells that have multiple selections. Is there a way that I can make it count all occurrences, whether it be a singular selection or part of a multi-selection cell?

    1. Hi! A multiple choice drop-down list creates a text string in the cell that consists of all the values that have been selected. To determine if text strings partially match, use these instructions: How to find substring in Excel. To count all occurrences, try a formula:

      =SUMPRODUCT(--ISNUMBER(SEARCH("zzzz",A1:A100)))

      1. Hi. I have successfully applied your methods in a worksheet, it is really clear and helpful, thank you very much.

        I noticed when I copied the answer to this question, Excel initially gave an error message, which I could resolve after changing the "," to an ";" (as in
        =SUMPRODUCT(--ISNUMBER(SEARCH("zzzz";A1:A100))).

  4. thank you for this wonderful lessons. however, i was able to create a dropdown list but could not enable multiple selections. i copied and paste the VBA code but i keep getting error . saying compile error invalid outside procedure.
    i dont know what to do.

    1. Hi! You have inserted the code incorrectly. You can use the sample file linked at the end of this article.

  5. Hi Alex,
    When I input the code from the "How to enable multi-selection functionality in protected sheet" section, I get the error message: Ambiguous name detected: Worksheet_SelectionChange." Any ideas on how to fix this?

  6. Nice in theory; however, I feel like there must be some details missing in your explanation, as I can't even get past the first step. I even tried replicating your exact example on a new spreadsheet (created shorter Table/list) and typing literally what you had down to brackets/no spaces/double quotes/square brackets/etc. (=INDIRECT("Table1[Items]") And I got an error message "The source evaluates to an error, do you want to continue"? Generally, I am highlighting/selecting a column or series of rows for the 'Source' field. Were the "Table1[Items]" just a placeholder for highlighting/selecting that actual options? I tried that too but always get the same error message.

  7. Hi Alexander,

    This page is amazing! Thank you so much!

    I'm trying to make it so that in certain columns, the options selected are separated by a comma, while in certain columns the options are separated by line breaks. However, when I try to run it, I receive an error message saying: Ambiguous name detected.

    1. I figured out if you do an "If Destination.Column = 4 Then / DelimiterType = "/" ...lines copied from section of website you want... End If" You can define a new delimiter for each column. You have to do it individually though. I couldn't group multiple columns into one if loop.

      1. Hi! You can set a different type of separator for a column. Here is an example of how you can set different separators for columns D and F.

        If Destination.Column = 4 Then
        DelimiterType = "; "
        End If
        If Destination.Column = 6 Then
        DelimiterType = " - "
        End If

  8. Great article. I only have one comment for possible refinement of instructions.

    Your example has the look up and the data on the same sheet. I often put look ups on a separate sheet.

    You instructions on where to put the VS code should say "Select the sheet where you'll be selecting from your look up". As it's worded, it's initially unclear whether to paste the code into the sheet with the look up or the data entry.

    1. Hi! The article notes that the code is worksheet specific, so be sure to add it to every worksheet where you want to allow multiple selections in dropdowns.

  9. Can the VBA scripts somehow we specified in module within a workbook rather than within specific worksheets of the workbook?

  10. Hi, Alexander. Your material is super! Just one question, a bit more complex :)
    I've created an Excel table with multiselection dropdown in a specific column. Based on this table, I've created a pivot table with several slicers, one of these slicers refers to the column D with multiselection dropdown.
    When in a certain cell in the column D several options are selected, but in another cell only one of these options is selected, in my slicer I see different options for filtering. For example: "X", "Y", but also "X,Y,Z". Is it possible to create a slicer that shows unique options only but allow filter them in all cells in column D regardless if in the same cell other options are selected together with the one I want to filter?
    How this could be done?
    Thanks in advance, Natalia

    1. Hi! "X,Y,Z" is a text string. You cannot search for unique elements within a text string. To do this, you must split the elements of the text string into separate cells. I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

  11. This was super useful! One thing i noticed about using the VBA for multiple selections is that is removes the ability to start typing the item name when searching for a dropdown item. I am working with a very long dropdown list, so this functionality really comes in handy. Is there any way to have the ability added back into the sheet?

    1. Hi! To create searchable drop-down lists in Excel, use data validation and named ranges. The steps are described below:
      1. Enter data into Excel columns. For example, if you want to create a searchable drop-down list of products, enter the product name in one column and the corresponding code in another column.
      2. Highlight the column with the data and click the “Formulas” button on the ribbon at the top of the screen. There, click “Define Names” button and name the named range. For more details on how to create a named range, read in this article: Excel names and named ranges: how to define and use in formulas.
      3. Highlight the cells for which you want the drop-down list to appear and click the “Data” button on the ribbon at the top of the screen. From there, select the “Data Validation” option.
      4. In the Data Validation dialog box, select “List” as the validation condition. In the Source field, enter the named range you created in step 2.
      5. Select the “Dropdown in cells” and “Ignore whitespace” check boxes.
      6. Click “OK” to close the dialog box.
      Now, when you click on a cell with a dropdown, a list of all the elements in the named range will be displayed. To search for specific items, you can type multiple letters and Excel will filter the list accordingly. Select an item and the corresponding value will appear in the cell.
      We hope this is helpful to you!

  12. Is there something that can be added to the code to also allow there to be 0 selections with item removal?

  13. Should the ability to select multiple items from the drop list remain if the cell with the drop list is unlocked but the sheet itself is protected? (It doesn't in my case. The celll reverts to only the last item selected.)

    1. My apology!! You have instructions for this--and they work like a charm!!!

  14. Unbelievable! This works like a charm, and I know nothing about VBA. Thank you sooooo much!

    Can you tell me how to retain formatting of dates when multiple items are selected? The options are, for example, Aug-23, Sep-23, etc., but when multiple options are selected, the format changes to 8/1/2023 and 9/1/2023. I've tried date formatting and custom formatting to no avail.

      1. Oh my word. You're a genius. Thank you for your quick response!

  15. Multiple selection worked in Excel 2016, but next to each row with multiple selection it throws an yellow error icon to its left saying:
    "The value in this cell is invalid or missing. ".
    It wants the value to match one of the listed items (eg. not multiple selection)

    1. The delimiter is comma+space. Nothing protected or locked.
      Source: Jhon, Dave, Jim (any of these is fine but adding more than one, Excel complains with the yellow error symbol. The selection functionality works though. Tried all 3 vba options - same complaint

  16. Hi Alexander,

    This page is incredible! Thank you so much!

    I'm having some trouble though isolating the multiple dropdown selection to one column. In your instructions, you said about adding the code "If Not Destination.Column = 4 Then GoTo exitError". The only thing I changed was 4 to 5 as that's the column I need the dropdowns in.

    However, it is still affecting my whole worksheet, so if I go to a dropdown in column 6 (F) where I only want a single selection, it allows me to put in multiple selections.

    Please can you advise where I've gone wrong and offer a solution?

    1. Hi! Pay attention to which sheet code you added this string of code to. The code works only on the sheet in which it is inserted.

    2. Sorry, I should have looked at the previous comments! For those that are having similar problems, I resolved the issue by inserting the following below the "If Destination.Count > 1 Then Exit Sub" line:

      If Not Destination.Column = 5 Then Exit Sub

      The worksheet now allows me to make my multiple selections in column 5 (E), and single selections in column 6 (F).

  17. Hi Alexander
    thanks for your comprehensive useful post. I have a problem here, I did everything same as you've mentioned here, and it works as multiple selection with removal enabled, so I save my worksheet as macro enabled, but after I close and reopen the file, it won't work as multiple selection but only single selection

  18. Hi Alexander, Thanks for the post very useful. Can we restrict selection of entries to 3 max based on comma's. May be stop at selected 3 entries and error when adding the 4th one. Appreciate if you could help with the code to use in the above example.

    1. Hi! As much as I'd like to help, I'm overloaded with some current projects at the moment and won't be able to take time for your task. Custom code changes are quite time-consuming.

      1. no problem. Thank you!

  19. Hey, is it possible to select a few items at one go from the dropdown list? For example, instead of selecting "Apples" , "Oranges", separately, is there a way to select multiple items at one go? So that Apples and Oranges will be displayed together at the same time without an extra step of selecting each item one by one. Perhaps like a checkbox before each item in the dropdown list.

  20. Hello. I used the VBA code in an Excel spreadsheet to allow multiple drop-down box selections in separate lines. Instead of DelimiterType = “,” I changed it to Delimiter = vbCrLf. I saved it as a xlsm file and it worked great. I got out of the spreadsheet and saved it. When I reopened to test it again, it would not work even though the same code was there. I have deleted the code and recopied it and also saved as a different xlsm file name. Do you have any ideas on what might have happened? I’m clueless and out of things to try. Any assistance would be appreciated.

    1. Hi! You can use the ready-made code from the example file (Selections in separate lines sheet) linked to at the end of this article.

  21. Is it possible to create a drop down list in a blank spreadsheet without creating a table?

  22. Hi,
    thanks for this, very useful.
    I am getting a bug on the removal function.
    With using a simple example where I have on my list Green Apples, Oranges, Apples; after selecting "Apples" again, the macro removes the first "Apples" - so it returns: Green , Oranges, Apples - which is not what I am expecting.
    is there a way to fix this?
    Thanks in advance,

    1. Hello! Thank you for your comment. We have changed the macro code. Now the values are deleted correctly.

  23. Hello First thank you - I was trying to read the comments on how to make this work for all sheets in a workbook rather then copying duplicate code onto each sheet (if I read the instructions correct)

    1. Hello!
      To run a macro on all worksheets in a workbook at once, the Workbook_SheetChange procedure is declared in the Workbook object and written in the ThisWorkbook code window. You can find the ThisWorkbook object in the VBAProject window below the worksheet list. Click on it and in the opened window add the code of the macro you need. You will need to add an additional parameter ByVal Sh As Object to the procedure.
      For example, instead of
      Sub Workbook_SheetChange(ByVal Destination As Range)
      write
      Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Destination As Range)

  24. I would like to use the "Multi-selection dropdown with item removal" but also allow for a write in on one option.
    For example: Location: Location A, Location B, Location C, Other Location:
    and the responder could click: "Location A" and "Other Location:", then go into the write in bar and type in "Garden" after "Other Location:"
    When I turn off the error alert in Data Validation I am able to do this but then the options start to duplicate themselves in the field: "Location A, Other Location:, Location A, Other Location: Garden"
    Thank you for your assistance

    1. Hi! The macro assumes that all values in the cell will be entered from the drop-down list. When you are correcting a cell manually, all of its content is considered as a new value and added to the existing text in the cell. The result is duplication.
      If you have "Location A, Other Location:" written in a cell and you want to add "Garden". When editing the cell, delete all text, type "Garden", and press Enter. You will get "Location A, Other Location:,Garden".

  25. This code would prove to be very useful for me, but for some reason doesn't work for me? I downloaded the example workbook, but unfortunately all the drop downs just revert to normal - only allow single selection as per the list validation.

    I am guessing there is a setting in my excel that needs correcting or perhaps the version i am using isn't compatible?

    Any advice would be appreciated.

  26. Hi,
    Would be of a help if there is a VBA macro to auto map data validation list map basis the column header. Say for example for columns D to L with header name, I have the list of values defined with the same header name from column P to X. Data validation is to be set for column D basis column P, column E basis column Q and so on basis the matching column names. The header range where the data validation and the header range where the list values provided should be user selection as this may vary basis the requirement.

    1. Hi! If I understand correctly, a special macro is required for your purposes. We offer a universal solution, where you can specify all the columns in which the macro should run.

  27. Great post.
    I found 1 issue, when the worksheet is protected.
    I applied your instructions for the following

    To restore protection, at the end of the code, before this line:
    exitError:

    Add this code:
    ActiveSheet.Protect Password:="password"

    This will unlock the worksheet on any list box that is single select.

    A list box defined as multi-select works as expected with this added following the protection additions
    If Destination.Address "$D$15" And Destination.Address "$C$86" And Destination.Address "$C$87" Then GoTo exitError

    I moved the ActiveSheet.Protect Password:="password" to right after Application.EnableEvents = True and now it no longer unlocks the worksheet for single list cells.

      1. Hi Alexander - I needed to use the fix provided by Chris Strabley as well. Suggest reviewing your provided code to remedy the sheet unlock when single validation cells selected.

        Great code by the way, helped me out significantly.

        1. Hi! We won't change the macro code for these suggestions, as most users don't need to. Also pay attention to the following paragraph: Enable multi-selection functionality in protected sheet. You can change the code for yourself, remembering about the security risk.

  28. Thank you for this. I am running into an issue that every time I add the code for a specific column If Not Destination.Column = 4 Then GoTo exitError on either the Multi item selection dropdown or or the multiselection dropdown with removal. The multiselection doesn't work any more... I am adding it after:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Destination As Range)
    Dim DelimiterType As String
    Dim rngDropdown As Range
    Dim oldValue As String
    Dim newValue As String
    DelimiterType = ", "
    If Destination.Count > 1 Then Exit Sub

    On Error Resume Next
    Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitError

    If rngDropdown Is Nothing Then GoTo exitError

    Thoughts?

    Thanks,

    1. NVM. I figured it out.

  29. Hi,
    Hope my message finds you well.
    I maked an excel file with VBA which works perfectly but stop working on others computer when i share it by eamil.
    How can i solve this issue please

  30. Hi Alexander Trifuntov (Ablebits Team)
    I want to know if there is a solution to use INDIRECT function on a multiple selected dropdonw list.

  31. You can change the VBA code and specify the column number in which the multiple selections will be made.
    After code:
    If rngDropdown Is Nothing Then GoTo exitError
    add code:
    If Not Destination.Column = 4 Then GoTo exitError

    Thanks
    But what if my column if more than one like column 15 and 16 and 19

    1. Just below that example in the article there is an answer to your question.

      If Destination.Column <> 15 And Destination.Column <> 16 And Destination.Column <> 19 Then GoTo exitError

      1. Infinitely Thanks.
        I realized you already solve it in "Multi-select dropdown for specific columns, rows, cells" section

  32. Excellent code, thank you!

    I need to have both multi-select drop down related VBA procedures you kindly allowed in your website work for one sheet because I have some columns here i want multi-select picks to allow duplicates and some columns to allow multi-select picks to NOT allow duplicates. Do i paste both procedures codes into the same code window? Do i need a call statement above the 2 procedures to call the one or the other depending on what column i am working in

    thank you!
    Craig

    1. Hi! Unfortunately, only one of the three codes available in the article can be placed on a single sheet.

      1. thanks very much for your prompt reply!

        Is you have any advice on where I can go to get some columns to allow multi select and dupes and some to not allow multi select dupes to work on same sheet, I would love to hear it!

        Craig

  33. Hello,

    When I pasted the code to different sheets, there is bug at '' If Destination.Count > 1 Then Exit Sub''.
    Could you please kindly explain to me what might be the reason behind?

    Best and thanks!

    1. Hi! This operator allows you to select only one value in the drop-down list. You are trying to add multiple values at once.

  34. Hi, is it possible to achieve similar "multi select drop down list" functionality in Office Script in Excel for WEB?
    or perhaps as an add-in for Excel for web?

    1. I already answered this question in the comments. Unfortunately, Visual Basic only works in the desktop version of Microsoft Office.

  35. I am trying to use this but the code is not running. I have the selections stored in a separate sheet in the same workbook, which, for other users, you have said should not change anything. I pasted the code in the worksheet-specific module and saved it, but the code does not work in the sheet. What might be the problem with this?

    1. When I download the example sheet, the code that is already in that sheet does not work also.

  36. I have encountered a complie error while I was using . May I know what's wrong? :

    Ambiguous name detected: Worksheet_SelectionChange

    1. Hi! The ambiguous name error usually means that you have two subs within a module of the same name. Make sure you have copied the macro code correctly. You can also use the example file linked at the end of this article.

  37. I have used the code on my sheet and it works beautifully. However, is it possible to create a pivot table showing the total count of the individuals choices from the list?

    When I try to create a pivot table, it is not separating the individual choices.

  38. Thank you for this article. I was able to leverage it to created a data validation with the ability to select multiple items. However, when I open it in 365, it will only let me select one item from the list. I have to open it in Desktop App to be able to select multiple items. Did I miss something or does it only work that way?

    Thank you!

    1. Hi! It has already been noted in the comment below that the browser-based version of Excel Office 365 does not support VBA. Therefore, macros only work in the desktop version.

  39. HI

    great article and was very helpful. just wondering if it is possible to put a filter on that will find and select just a single item in the dropdown. for example (using your scenario) if i multiple dishes (lets say 30) and in them i had various ingredients added to the multiple drop down. but i only wanted to search for the dishes that had tomato in them. is there a way to just filter them? at the moment it is only letting me filter by what is in the dropdown of each row.

    hope that makes sense.

    regards

    1. Hi! The macro is used in the usual standard Excel drop-down list, in which no filters can be used. You can manage the content of the named range that is used in the drop-down list. If this is not what you wanted, please describe your question in more detail.

      1. HI
        How to write a code to print all the dropdown list values when we click any one value in that dropdown all the values must be printed in that dropdown list in excel

  40. Very useful; thanks. Do you have a version that works in an O365 browser-based instance of Excel?

    1. Hi! Unfortunately, the browser-based version of Excel Office 365 does not support VBA. Therefore, custom VBA functions and macros are not possible there.

  41. This code works perfectly but has one problem when you have the list with similar values.
    ex. Semi-Automated, Automated
    It checks string if its contains not exact match in the list

    For that purpose need such condition:

    If Not IsError(Application.Match(newValue, Split(oldValue, ","), 0)) = 0 Then
    Target.Value = oldValue & ", " & newValue
    Else:
    Target.Value = oldValue
    End If

    1. Hi! Please explain what you see as the problem. Similar values are different values. And naturally, the list values should not have signs that are used as delimiters.

      1. Alexander , thank you for your comment!

        By similar values I mean the values that have same words.

        example: Red Apple, Apple

        So, this algorithm, in case uncheck item 'Apple' will find that old value: 'Red Apple, Apple' contains 'Apple',
        as a result I get 'Red'

        1. Finally I came to this

          If Oldvalue = "" Then
          Target.Value = Newvalue
          Else
          Dim arr As Variant
          arr = Split(Oldvalue, ";")
          Dim str As String

          If Not IsError(Application.Match(Newvalue, arr, 0)) = 0 Then
          Target.Value = Oldvalue & ";" & Newvalue
          Else:
          Target.Value = Join(Filter(arr, Newvalue, False), ";")
          End If

  42. Hello, the above CODE 'Multi-selection dropdown with item removal', works excellent, however it turns on the rule for all my dropdowns in the excel. how do i limit this code only to cell D5?

    1. Good Morning -
      This works great! BUT,.....I would like to be able to select multiple items at one time from the drop-down list rather than returning to the dropdown selector each time. I need to remove the repetitive steps with each item selected. Is there a way to index the selected items so the user can begin typing the first few letters and the cursor move to the entry in the table?
      Thank you for this great tutorial!

  43. Very clear, thanks! One question though, is it possible to order the answers automatically?

    For instance, if you have a dropdown with values: 1, 2 and 3. Now when you first select 2 and then select 1, the order is 2, 1. I would like to have it automatically change to order 1, 2, based on the order of the initial list of values. Regardless of the selection order.

    1. Hi! Sorting words in a cell is a separate task that must be handled independently from the multiple selection macro.

  44. Hi! I'm trying to use the 'Item Removal' code, but my drop down is on a separate sheet than my list... Can you help me with modifying the code for this?

    1. Hi Liz,

      The current code completely covers your case! Simply, create your dropdown as described in how to make dropdown from another worksheet. And then, add the code to the sheet where your dropdown is located. In the sample worksheet that is available for download at the end of the post, the source items and the 'dropdown with removal' are on different sheets, and all works beautifully.

  45. Is there a way to use COUNTIF, for example, to calculate how many dishes contain cheese? When I've tried, COUNTIF doesn't calculate for cells that contain more than 1 selection.

  46. I love that I can make multiple selections from a drop down.
    The problem I am having is that when I save the workbook, using .xlsm, close the workbook, then go back to it, the multiple selection option is no longer working.
    What am I doing wrong?
    Thanks,

  47. Why doesn't the multi-select drop-down work when the sheet is protected, but works when the sheet is unprotected? I have made sure those cells are not locked in the sheet so the row formats can be expanded if more than one item is selected. How do I fix this problem?

    1. Hi!
      If a worksheet is protected, by default, all cells are locked, even those that were previously unlocked. This means that any VBA code that tries to reference or access unlocked cells will not work when the worksheet is protected.

      In order to access unlocked cells that have validation rules applied, you will need to unprotect the worksheet first, execute the code that references the unlocked cells, and then protect the worksheet again.

  48. How do you make it so that it targets a specific column?

    1. You can change the VBA code and specify the column number in which the multiple selections will be made.
      After code:
      If rngDropdown Is Nothing Then GoTo exitError

      add code:
      If Not Destination.Column = 4 Then GoTo exitError

      In this case, 4 means column D. In the other columns, the dropdown list will work as usual.
      Thanks to K N for clarification!

  49. When I share the sheet with Co-workers, they are not able to select multiple items. It reverts back to a single selection. But when I am in the document I can select multiple.

  50. Within the same sheet where multi-selection code is installed, is there a way to specify which drop-down use multi-selection and which do not?

    1. If say for example you have lists in A, B & C and only want to apply this multi-select to those in column B, simply add the code below before the On Error Resume Next line.

      If Not Destination.Column = 2 Then Exit Sub

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