How to make Excel drop down list with multiple selections

The article shows how to create an Excel drop down that allows users to select multiple items with or without duplicates.

Excel has come a long way since its inception and introduces more and more useful features with each new release. In Excel 365, they've added the ability to search within data validation lists, which is a huge time-saver when working with large sets of data. However, even with this new option, out-of-the-box Excel still only allows selecting 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. With the ability to prevent duplicates and remove incorrect items, this feature can streamline data input and improve accuracy in your Excel spreadsheets.

Excel drop down list with multiple selection

How to make Excel drop down with multiple selections

Creating a multi-select drop down list in Excel is a two-part process:

  1. First, you make a regular data validation list in one or more cells.
  2. And then, insert the VBA code at the back end of the target worksheet.

It also works in the reverse order :)

Create a normal drop-down list

To insert a drop down list in Excel, you use the Data Validation feature. The steps slightly vary depending on whether the source items are in a regular range, named range, or an Excel table.

From my experience, the best option is to create a data validation list from a table. As Excel tables are dynamic by nature, a related dropdown will expand or contract automatically as you add or remove items to/from the table.

For this example, we are going to use the table with the plain name Table1, which resides in A2:A25 in the screenshot below. To make a picklist from this table, the steps are:

  1. Select one or more cells for your dropdown (D3:D7 in our case).
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Allow drop-down box, select List.
  4. In the Source box, enter the formula that indirectly refers to Table1's column named Items.

    =INDIRECT("Table1[Items]")

  5. When done, click OK.
Create a data validation list from a table.

The result will be an expandable and automatically updatable drop-down list that only allows selecting one item.

Tip. If the method described above is not suitable for you for some reason, you can create a dropdown from a regular range or named range. The detailed instructions are here: How to create Excel data validation list.

Insert VBA code to allow multiple selections

This is the core part of the process that does the magic. To turn a regular single-selection picklist into a multi-select dropdown, you need to insert one of these codes in the back end of your target worksheet:

To add VBA code to your worksheet, follow these steps:

  1. Open the Visual Basic Editor by pressing Alt + F11 or clicking the Developer tab > Visual Basic. If you don't have this tab on your Excel ribbon, see how to add Developer tab.
  2. In the Project Explorer pane at the left, double-click on the name of the worksheet that contains your drop-down list. This will open the Code window for that sheet.

    Or you can right-click the sheet's tab and choose View Code from the context menu. This will open the Code window for a given sheet straight away.

  3. In the Code window, paste the VBA code.
  4. Close the VB Editor and save your file as a Macro-Enabled Workbook (.xlsm).
Insert VBA code to select multiple items in dropdown list.

That's it! When you go back to the worksheet, your drop-down list will allow you to select multiple items: Excel drop-down list to select multiple items.

VBA code to select multiple items in dropdown list

Below is the code to make a data validation list that allows selecting multiple items, including repeated selections:

VBA code to select multiple items in Excel dropdown
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   If Intersect(Destination, rngDropdown) Is Nothing Then 'do nothing Else Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue = "" Then 'do nothing Else If newValue = "" Then 'do nothing Else Destination.Value = oldValue & DelimiterType & newValue ' add new value with delimiter End If End If End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

How this code works:

  • The code enables multiple selections in all drop down lists on a particular sheet. You do not need to specify the target cell or range reference in the code.
  • The code is worksheet specific, so be sure to add it to each sheet where you want to allow multiple selections in drop down lists.
  • This code allows repetition, i.e. selecting the same item several times.
  • The selected items are separated with a comma and a space. To change the delimiter, replace ", " with the character you want in DelimiterType = ", " (line 7 in the code above).

Note. The same character cannot be used as both the delimiter and part of the dropdown items. In our code, the default delimiter is a comma followed by a space (", "), so this combination of characters should not appear anywhere within the dropdown items themselves to avoid conflicts. If you change the delimiter to a single space (" "), bear in mind that the code will only function correctly for single-word items, failing to handle multi-word items containing spaces.

Excel multi-select dropdown without duplicates

When selecting from a large list, users can sometimes pick the same item more than once without noticing. The code below solves the problem of duplicates in a multi-selection data validation drop down list. It lets users pick a particular item only once. If you try to select the same item again, nothing will happen. Pretty cool, right?

VBA code to create multi-select drop down with no repeats
Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType 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   If Intersect(Destination, rngDropdown) Is Nothing Then 'do nothing Else Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or _ InStr(1, oldValue, DelimiterType & newValue) Or _ InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then Destination.Value = oldValue Else Destination.Value = oldValue & DelimiterType & newValue End If End If End If End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

Multi-selection dropdown with item removal

When users need to select multiple options but can make mistakes or change their minds during the selection process, a multi selection dropdown that allows for the removal of incorrect items can be a lifesaver.

Consider a scenario where you need to assign multiple tasks to team members using a drop-down list. With Excel's default functionality, the only way to remove an incorrectly assigned task is by clearing the entire contents of the cell and starting over. With the ability to remove individual items from the selection, the team can effortlessly modify task assignments without confusion or errors.

The code below implements the item removal functionality in a simple and intuitive way: the first click on an item adds it to the selection, and a second click on the same item removes it from the selection.

VBA code to create multi-select drop down that allows removing selected items
Option Explicit Private Sub Worksheet_Change(ByVal Destination As Range) Dim rngDropdown As Range Dim oldValue As String Dim newValue As String Dim DelimiterType As String DelimiterType = ", " Dim DelimiterCount As Integer Dim TargetType As Integer Dim i As Integer Dim arr() As String   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   TargetType = 0 TargetType = Destination.Validation.Type If TargetType = 3 Then ' is validation type is "list" Application.ScreenUpdating = False Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list oldValue = Replace(oldValue, DelimiterType, "") oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "") Destination.Value = oldValue ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, newValue & DelimiterType) Or InStr(1, oldValue, DelimiterType & newValue & DelimiterType) Then arr = Split(oldValue, DelimiterType) If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then Destination.Value = oldValue & DelimiterType & newValue Else: Destination.Value = "" For i = 0 To UBound(arr) If arr(i) <> newValue Then Destination.Value = Destination.Value & arr(i) & DelimiterType End If Next i Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType)) End If ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then oldValue = Replace(oldValue, newValue, "") Destination.Value = oldValue Else Destination.Value = oldValue & DelimiterType & newValue End If Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) If Destination.Value <> "" Then If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2) End If End If If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1) End If If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1) End If DelimiterCount = 0 For i = 1 To Len(Destination.Value) If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then DelimiterCount = DelimiterCount + 1 End If Next i If DelimiterCount = 1 Then ' remove delimiter if last character Destination.Value = Replace(Destination.Value, DelimiterType, "") Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "") End If End If End If Application.EnableEvents = True Application.ScreenUpdating = True End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

The below demo highlights how the multi selection dropdown with removal functionality works in Excel. The users can select multiple options from the data validation list and make adjustments on the fly. A streamlined and effective approach to managing selections!
Select and remove multiple items in Excel dropdown.

How to make a multiple selection dropdown with custom delimiter

The character that separates items in the selection is set in the DelimiterType parameter. In all the codes, the default value of this parameter is ", " (a comma and a space) and it is located in line 7. To use a different separator, you can replace ", " with the desired character. For instance:

  • To separate the selected items with a space, use DelimiterType = " ".
  • To separate with a semicolon, use DelimiterType = "; " or DelimiterType = ";" (with or without a space, respectively).
  • To separate with a vertical bar, use DelimiterType = " | ".

For example, if you change the delimiter to a vertical slash, your multi-select picklist will look as follows: A multi-selection dropdown with a custom delimiter

How to create dropdown with multiple selections in separate lines

To get each selection in a separate line in the same cell, set DelimiterType to Vbcrlf. In VBA, it is a constant for the carriage return and line feed characters.

More precisely, you change this code line:

DelimiterType = ","

to this one:

DelimiterType = vbCrLf

As a result, each item that you select from the dropdown list will appear in a new line: A dropdown list with multiple selections in separate lines

How to create multi-select dropdown for specific columns, rows, cells and ranges

All the codes described in this tutorial work across an entire sheet. However, you can easily modify any of the codes, so it only applies to specific cells, rows, or columns as needed. For this, find this line of code:

If rngDropdown Is Nothing Then GoTo exitError

Add immediately after it, add a new line specifying where to allow multiple selections, as explained in the below examples.

Multi-select drop-down for specific columns

To allow selecting multiple items in a certain column, add this code:

If Not Destination.Column = 4 Then GoTo exitError

Where "4" is the number of the target column. In this case, the multi-select dropdown will be only enabled in column D. In all other columns, dropdown lists will be limited to a single selection.

To target several columns, specify their numbers using this code:

If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError

In this case, the multi-select dropdown will be available in columns D (4) and F (6).

Multi-selection dropdown for certain rows

To insert multiple drop-downs in a specific row, use this code:

If Not Destination.Row = 3 Then GoTo exitError

In this example, replace "3" with the row number where you want to enable multi-select dropdowns.

To target multiple rows, the code is as follows:

If Destination.Row <> 3 And Destination.Row <> 5 Then GoTo exitError

Where "3" and "5" are the rows where selecting multiple items is allowed.

Multiple selections in specific cells

To enable multiple selections in particular cells, add one of the below code lines.

For a single cell:

If Not Destination.Address = "$D$3" Then GoTo exitError

For multiple cells:

If Destination.Address <> "$D$3" And Destination.Address <> "$F$6" Then GoTo exitError

Just remember to replace "$D$3" and "$F$6" with the addresses of your target cells.

Multi-select drop-down for specific range

To limit your multi-select dropdown to a particular range, replace this line of code:

If Intersect(Destination, rngDropdown) Is Nothing Then

with this one:

If Intersect(ActiveCell, Range("C3:D10")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then

The range of interest is specified directly in the code (C3:D10 in the above example). This modification offers a more efficient approach to handing ranges - instead of individually listing 16 cells, you use a single range reference.

How to enable multi-selection functionality in protected sheet

To enable a multi-select dropdown functionality in a protected worksheet, simply insert the following code into the sheet where you've added the primary code. This "protected sheet" code should be added after the main code.

VBA code to allow multiple selections in protected sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect password:="password" On Error GoTo exitError2 If Target.Validation.Type = 3 Then Else ActiveSheet.Protect password:="password" End If Done: Exit Sub exitError2: ActiveSheet.Protect password:="password" End Sub

Before adding this code to your worksheet, remember to replace "password" with the actual password you used to protect the sheet. And this is the only change that needs to be made. The code will automatically detect the presence of a dropdown list in a given cell and allow editing of that cell. In all other cells, editing will remain restricted.

Note. Please be aware that including your real password in the code could lead to a security risk. To ensure the safety of your workbook, store it in a secure location that is protected against unauthorized access or use.

So, there you have it - an Excel dropdown list with multiple selections. Adding this awesome feature to your spreadsheets will increase the accuracy of your data input and help you get your work done faster!

Practice workbook for download

Multi-selection dropdown - examples (.xlsm file)

Table of contents

622 comments

  1. Hi there! This is a fantastic feature but I’m having a hard time thinking through the best way to create a pivot table based on the individual components. For example, I don’t want to know the count of “Bread, Eggs, Butter” I want to know how many times Eggs are on the list and for what dish. I know I can do =countif(range, “*”&”Eggs”&”*”) for the count but I’m not able to do that in a pivot table to do additional analytics. I appreciate any guidance you’re able to provide! Thanks!

    • Hi! I don't really understand what your question has to do with the article above. If you want to count how many times a specific word occurs in a range of text cells, use the SUMPRODUCT function and these guidelines: How to find substring in Excel. For example,

      =SUMPRODUCT(--ISNUMBER(SEARCH("eggs",B1:B10)))

      In a pivot table, you can't get any analytics on a part of a text string. You need to split the text into individual cells.

  2. This worked perfectly when I first tried it out, many thanks! However, when I got an update of MS Office, the code does not work for 64 bit - any way this could be converted easily?

    Thanks!

  3. Thank you this is perfect!!

  4. Thank you for your help! I inserted the primary code into my spreadsheet and it worked as described. However, when I tried to add in the additional code to enable a multi-select dropdown functionality in a protected worksheet, I would get the following error: Compile error: Ambiguous name detected: Worksheet_SelectionChange
    It would specifically highlight the first line of the added code which followed the primary code. I tried both protecting the sheet and workbook individually and together, and each time I would get the error. The last group in the primary code has the same "Private Sub Worksheet_SelectionChange (ByVal Target As Range)" language. I tried replacing that with the additional code, but that only resulted in the multi-select dropdown no longer functional. I have updated the password options as instructed.
    I don't see this in the practice workbook, and I'm not sure how to fix. Any help you can provide would be much appreciated. Thanks!

      • Sorry, the primary code I used was for the "Multi-selection dropdown with item removal" option. So that only has one occurrence of "Private Sub Worksheet_Change". The ending version is "Private Sub Worksheet_SelectionChange". I combined this version of the primary code with the "multi-select dropdown functionality in a protected worksheet", leaving 2 occurrences of "Private Sub Worksheet_SelectionChange".

  5. I am trying to create a shared document for work using the Multi-selection dropdown with item removal and it works great however it doesn't cross over to the online version (which all my colleagues would see).

    It still lets me select from the list, the most basic form of it works, but the mutli selection and item removal doesn't seem to be translating over.

    Any idea how I can fix this?

  6. Hi, thanks for the code. Selecting multiple options works smoothly, but if by mistake I select a wrong item in the list, how can I remove it? nothing seems todo the trick. thanks in advance

    • my bad, apologies, I used the wrong code snippet. all works perfectly

    • Hi! To remove the wrong item from a cell, you can use the code: Multi-selection dropdown with item removal. Or you can manually correct the data in the cell because the dropdown list creates a text string. You can edit it in the formula bar.

  7. Hello,
    This code works beautifully, until I upload my spreadsheet to onedrive so that I can share it with colleagues, it then loses the option to select multiple options from the drop down list and only allows you to choose one, Is there a way to change the VBA code for a share point excel spreadsheet, Or is there another way to add a multiple selection list for one that opens on chrome ? Any help would be appreciated

  8. Hello,

    This is all great information!! So far is working perfectly! I do have a question, Is there any code that could eliminate your selection from the drop down box in the next row? For example, If I have multiple people using the excel spreadsheet and we have 2 rows with employee names in the drop down list, 1 person picks 3 names from the list and the next person in the next row needs to see what names they cannot pick.

  9. Thank you very much for providing this code, it works just how it's suppost to! I used the dropdown with multiple selections in separate lines code and realized, that the code does not keep the entries in the order of the drop-down menu. For instance: If you select Apricot and then Bread crumps, it will show it in the right sequence.If you de-select Apricot, it correctly only shows Bread crumps. However if you then select Apricot again, the result will be displayed at Bread crumps, Apricot. Is there a way to keep the selections in the order of the drop-down?

    • Hello! Since the drop-down list creates a text string in the cell, you need to write special VBA code to sort the words in the text string within the cell.

  10. I am having a problem getting the code to work that allows the multi select in a protected workbook. Any suggestions? I’m not getting a specific error, the multi select functionality still doesn’t work when the additional code is added and it is updated with my password. Thanks!

    • Hi! Unfortunately, this information is not enough to understand your task. Carefully follow the recommendations from the article above. You can use the sample file. See the link at the end of the article. Or explain your problem in detail. What do you want to do and what code are you using.

  11. The drop down list with multi- selection is great.

    Is there any code that could eliminate your selection from the drop down box and repopulate it in the next cell over when it is clicked?

  12. Hello,

    Thanks so much for these codes. It works perfectly. Would it be possible to enhance the code to provide user with long description in the drop down list, but returns the short abbreviation in the worksheet? For example, when entering a state, user types in "A" and the drop down shows "Alaska, Alabama, Arizona, Arkansas". If user selects "Alaska", the worksheet would record "AL".

    Best Regards.

    • Hi again,

      please ignore. I figured out how to accomplish what I needed. In the line "newValue = Destination.Value", I simply replaced it with "newValue = Left(Destination.Value, InStr(1, Destination.Value, "-") - 1)". Of course, the state list would have to be entered as "AK-Alaska"; "AL-Alabama", ... Drop down list would show "AL-Alabama", but when user selects it, worksheet will show "AL".

      Many many thanks for the codes.

  13. Hi
    Thanks for the code. It works perfectly and it saved a lot of our time.
    Just wondering if it is possible to have different text colour for each phrase in one cell.
    Let say in your example, whenever 'Milk' is selected, it shows with a red text colour and the rest item are shown black. Is this something possible?
    BTW, I already tried the cell rule but it will change the text colour for entire cell not one item.
    Many thanks

  14. First, thank you for sharing this code. I'm excited to see it in use with my current worksheet. However, I have a question first: where do we insert this code? You wrote, "insert the VBA code at the back end of the target worksheet" without details for what that means.

  15. Hi, thanks for our tutorial!
    May I know if I can filter those "multiple selections" data that we just created?

    • Hi! The drop-down list with multiple selections creates a text string. To create a filter on a part of this text string, you can try using the advanced filter options as described in these instructions: Filter text data.

  16. I have just downloaded your file and I dont have any error. Your example is working perfectly on every tab without error.

    I just don't get why yours is perfect but then your code injected in ours won"t behave exactly the same way.

    PS : This should be the last post, apologies for multiple posts for the same topic.

  17. Hi,

    Forgot to mention the nature of the error. It is a data validation error. It behaves as if the multiple options selected could not match when any of the options defined in the drop down list. In a way, it is true if the validation compares the value of the cell built with multiple entries with a cell defining the drop down list. In this case, there can't be a match hence we get an error flag.

    Surprisingly, looking at your example, it seems you don't meet the same behaviour.

    Best regards,

  18. Hi,

    Thank you for this valuable multiselection piece of VBA. Unfortunately, I have the same issue with an error flag on each cell now. The multi selection is working and even with your code enabled, there is no error flag if you select one option only. The error only comes when you select at least 2 options from the drop down list.

    I can't appy BThompson's suggestion as I only have 1 table.

    Any other suggestion please?

    Best regards,

    PS : I guess the filtering on this column with multiple options selected will not allow filtering by one of these options only, right? Any suggestion to allow "smart" filtering once multiple options are entered in a single cell?

    • Hi! You said earlier that the sample file you downloaded works without errors. So, the problem is in your file. Check what VBA codes are in your file. Maybe some other code is preventing the macro from working. You may have characters in your data that you also use as delimiters. We discussed this in the comments above. I can't check your file.
      The drop-down list creates a text string. To create a filter on a part of a text string, you can try using the advanced filter options as described in this guide: Filter text data.

  19. Hello, I used your script and it works. However, the macros won't run for excel online. Do you have any idea how to fix it?

    • Hi! I already answered in the comments above that Excel Online does not work with VBA. Therefore, macros and user-defined functions do not work there.

  20. This is great! One issue, I am now getting a data validation error flag on every cell where I have selected multiple items from the list. The field type information is - Data type: List Restriction: Value must match one of the listed items. Blanks are ignored and are automatically valid.

    I tried unchecking the 'show error alert after invalid data is entered' box in data validation but the error flags are still appearing.

    • Hi! Unfortunately, I don't see your data and the cause of your problem. Check if the dropdown list works without the code. Then carefully copy the code to your worksheet. Or use the example file linked at the end of the article.

    • I ran into the same error. Try spacing your tables apart at least one column. Otherwise, your individual tables will be treated as part of the larger, original table.

      • I have the same data validation issue. It seems isolated to when your data is in a table instead of just a collection of columns

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