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

620 comments

  1. The codes are great, however the multi selection option didn't work when you protect the sheet and allow multi selection cells for editing.
    Any help on that?

    • Hi! I can't check your worksheet and I can't check your VBA code. However, this is a question that has been asked many times in the past. You may be able to find the answer in the comments section below.

  2. This code is great! I'm wondering if there's a way to provide a default text display that goes away when something on the list is selected.

  3. This isexactly what I was looking for
    And was super easy to do
    thank you for that
    I love you

  4. How would I get the vba to look at column header instead of number? I have a column called "Area of Impact" and if I add or move columns, the VBA wont stay on that column.
    Thanks for the code by the way, exactly what I was trying to do.

  5. Thanks for this. It worked great. However, when i shared the file it no longer worked. But it still works on the original file. Any thoughts? I am trying to share a Onedrive link for others to collaborate on the same file.

  6. This worked (it took me a minute because I put the code on the sheet that had the list values first instead of the sheet that I was putting the selections on - I left the code on both sheets and there's no problem, so that's nice too!).

    I am super excited about the multi-select - with removal! - because I can have one column instead of 2 (one to say multiple items and a second to list out multiple items). This will make for a smaller, less intimidating spreadsheet for the people who have to fill it out - and it cuts down on entry errors. Win win all around!

    Thank you!!

  7. This is really clever and useful work! Thank you so much for sharing. I've used the code for multiple selection with item removal, and it works just fine. However, as soon as I select more than one item to appear in the cell, there's a cell data warning triangle sign, telling me this: "The value in the cell is invalid or missing. Click on 'Display Type Information' for specific details.", and then, this Field Type Information says this: "Data Type: List. Restriction: Value must match one of the listed items." Do you happen to know what this is about?

    • Hello Constantine!
      Unfortunately, I was not able to reproduce your problem. VBA code to select multiple items in dropdown list works for me without errors. Note that the same character cannot be used as a delimiter and part of a drop-down list at the same time. That warning is in the article above.
      If this does not help, explain the problem in more detail. Give an example of the data you use in the drop-down list, what list separator you use.

      • Thank you for your response, Alexander! On my end I'll play a bit more with the scripts and data in a new spreadsheet, sometime next week, to see if I can get a better feel for the whole idea. I think I need to redo everything from scratch, to avoid any errors. I'll get back to you if/when I have clearer questions.

  8. I'm not sure this can be done but, I'm trying to create a multiple entry dropdown box, but for each entry I select I want to be able to select specific information from another multiple entry dropdown box. For example, I am trying to identify a list of claims in the first cell, but as I enter each selection, I want to be able to "attach" multiple bases to that claim from the cell next it.

    Like Cell D2 my first selection is CONSTRUCTIVE DISCHARGE. When that selection is entered, I want to be able to select from Cell E2 bases like AGE, DISABILITY and NATIONAL ORIGIN.
    Then, I would like to make a second entry in Cell D2 like DISCIPLINARY ACTION. And when that is entered I need to select bases from E2 like: DISABILITY and RACE

    Right now, I have to list one Claim in D2 then add the Bases in E2, then drop down to D3 and enter another Claim and then enter the Bases for that claim into E3. That requires me to merge all of the other cells once I am finished entering the Claims and Bases, so that each claim and corresponding bases are captured under the same case number. But that's a pain because once I start merging cells, I can't sort the entire worksheet.

    It's probably impossible to do. Or maybe I need to use a combination of dropdown lists and picklists. I don't know. Any advice would be appreciated.

    Thanks

    • Thank you, Alex. I'll look at the Dynamic Dependent Dropdown Box link and I will probably have to go out and get a VBA code for Dummies book so I can learn ho to do this. It's kind of funny that I'm going through all of this time and effort to create a function on my spreadsheet that is designed to keep me from expending excess time and effort when entering data. But, I'm stubborn that way and it will be worth it in the end.

      Thanks again for your quick response and assistance.

  9. Thanks for this detailed explanation. I was wondering if it would be possible to "connect" a selection to another cell - here are the details:
    I want to have a multiple choice dropdown in column D (this works) and a multiple choice dropdown in column E (works as well). Now, if column D indicates the profession (e.g. doctor), I want to have a multiple choice dropdown in column E (indicating area of expertise) that just shows me the choices of areas a doctor might have expertise in (e.g. immunology, cancer).
    Does this seem feasible? If yes, how would one accomplish this?
    Thanks in advance.

  10. Hello,

    I am by no means tech savvy, but your instructions were clear, and I was able to create a spreadsheet with multiple drop downs and I am very much appreciative of you. I would like to be able to change text in a cell after selecting a drop down to customize it. For example, my drop downs include:

    Background, pg.
    Policy, pg.
    Policy, Section, pg.

    I would like to be able to change text within a cell and not necessarily within the table for example

    A4. Background, pg. 1
    A5. Policy, pg. 6
    A6 Policy, Section 1a, pg. 6

    Currently, when I change the text, or strikethrough something (to keep history) i.e., Background, pg. to Background, pg. 1 they will both appear in the drop down. When I try to delete one, Background, pg. 1 will appear 2x along with Background, pg. Also, if I try to strikethrough an item on the list it will duplicate the whole list.

  11. Hi,

    I have inserted the code and it works perfectly fine for me and for several other colleagues but for some of them it doesn't. The file is stored in a Teams folder but always opened in the desktop app. Any idea on what the reason for this could be? Could this be settings related on their end? Pleased to learn and thanks in advance!

    Toon

  12. Hello!

    Great code! I was wondering if there's a way to replace values in the dropdown selection after selections have been made. For example, if I have in a cell: "Bread crumbs, Celery, Onion," I want to change "Bread crumbs" to "Crumbs" in the source sheet and see the same change reflected in the dropdown without having to clear the dropdown and reselect all values again. Is there a way to do this?

    Regards - Otto

  13. Hello! When attempting to use this code I have been getting "Run Time Error '50290': Method 'EnableEvents' of object '_Application' failed" and then when I click debug it highlights the "exitError: Application.EnableEvents = True" line. What would be the solve here? I copy and pasted the code exactly into VBA, so I can't figure out where it's going wrong.

    • Hi! Try to insert the code correctly again. You can also insert the code from the sample file linked at the end of the article.

  14. Hi! This works great - but not in the web version of excel in office 365 / sharepoint. Is there a way to enable this for web based excel?

  15. I have two columns that have are using a drop down list and the code is working on one of the columns but not on the other. Is there something I am missing? My understanding is the code should work on any lists on the sheet?

    • Hi! I don't know what code you have installed. The article above suggests codes that work on entire worksheet, but can also work in individual columns, rows and cells: Multi-select dropdown for specific columns, rows, cells and ranges.

  16. I can't get the code to work (multi select with delete) when I have the worksheet protected. I added the password sub code you provided to the Sheet2 code (pasted between Option Explicit and Private Sub Worksheet_Change(ByVal Destination As Range)). Is there anything I need to add to UserInterfaceOnly to get this to work?

    • Hi! It is enough to follow all the instructions given in the article above. Or use the sample file from the link at the end of the article.

      • Hmm. I have tried it several times and can't get the code to work, even on the sample file. I can only ever select one item from the drop down when the form is protected. Are there any other trouble shooting areas I can look to?

  17. Hello, thanks for this detailed explanation. It is very helpful. I am using the VBA code to select multiple items in dropdown list. I was wondering if there is a way to count how many times each word/slection is used. I tried using the =COUNTIF but as soon as a word/slection is repeaded from the dropdown list, it brings the count to 0.

  18. Hello, thantks for the content, is very well written and the code works perfectly.
    I have one question, is it possible to filter those informations inividually? Because by using your code I've noticed that the informations are classified as one new variable, but I would like to obtain a filter in the colum that would get for me all the lines that have "eggs" for example. How can I do it?

    Thanks

  19. Hello! This isn't working for me... Does the data validation have to be in the same sheet as the drop down menu? I used the additional code: "If Not Destination.Column = 4 Then GoTo exitError" in the correct sheet and column but it won't apply to the drop down menu.

    • Hello! Unfortunately, I have no way of knowing what you did or how you did the code installation. The code should be on the same sheet as the drop-down list. Follow the instructions carefully, or you can download the sample file at the end of the article.

  20. Sorry. I have one more question. Can I use two different codes on the same sheet (i.e., multiple selection dropdown with removal for cells C:10:D50 AND a multiple selection dropdown with repetition for cells H10:H50). I can get the first own working, but when I drop the code for the second one, nothing works. I know it's in the code but I just don't know how to fix it. Thanks

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