Select Multiple Items from a Drop Down List in Excel
A drop-down list is a great tool for organizing predefined data and allowing users to select from it.
With a drop-down list, there is reduced risk for input errors, and it allows users to save time manually entering information into a worksheet.
This makes drop-down lists the perfect tool for allowing users to input information into a form.
The only drawback is that ordinarily, Excel will only allow one input to be entered into an ordinary drop-down list.
However, with a little bit of work, you can create a drop-down list that will allow you and other users to Select Multiple Items from a Drop Down List in Excel
Create a Drop-Down List
The first step to creating a drop-down list that allows multiple selections is simply to create a drop-down list in the first place.
Start by creating a column of data from which the inputs for the drop-down list will be sourced.
Place each item that will appear on the list in its own cell.
This data can be located on the same worksheet or on another one if you prefer to keep a less cluttered appearance.
In the latter case, you could simply add a new worksheet to the same workbook, and the result will be the same, so the choice is yours.
Next, select the cell where you would like the drop-down list to appear and navigate to the “Data” tab and select “Data Validation” from the “Data Tools” group or from the drop-down menu.
This will bring up the “Data Validation” dialog box, where you will select “List” from the “Allow” menu, and under “Source,” you will enter the cells containing your range of data.
The easiest way to do this is to click on the down arrow on the right-hand side and then select your range of cells manually.
Simply click and drag over your range of cells and then select the arrow icon on the “Data Validation” menu.
This will automatically enter the selected range, and if it looks correct, select “OK,” and Excel will automatically create a drop-down menu including the elements located in the selected cells.
Allowing Multiple Selections of Items in DropDown list
You now have a drop-down list with all of the needed elements.
However, you are still limited to only one selection at a time.
Unfortunately, Excel does not have any built-in method for permitting more than one selection at a time.
In order to allow more than one selection, you will need to enable this option in Excel’s back end by using the VBA code.
VBA, or, Visual Basic for Applications, is the programming language on which Excel and Microsoft’s other Office applications are run.
This means that with just a few lines of code, we can make it so that Excel will allow multiple selections in a drop-down list, and here is how.
Use the Developer Tab
If you don’t already have the “Developer” tab on your ribbon, add it now.
You will need this tab in order to access the “VB Editor.” To add the “Developer” Tab, select “File” on the top-left of the ribbon and options near the bottom of the menu.
From here, navigate to “Customize Ribbon,” and on the “Choose commands from” menu, select “Main Tabs.”
Now select “Developer,” “Add,” and “OK.” The “Developer” tab will now be on your ribbon.
Add the VBA Code to a Module
You will now need to add the correct VBA code to a module in the “VB Editor.”
Select this by navigating to the “Developer” tab and selecting “Visual Basic” under the “Code” group.
This will bring up the VB Editor.
On the left-hand side, you should see the “Project Explorer” pane, where you will see your worksheets.
Double-click your worksheet from this list, and it will open a window for your current worksheet.
Here you can enter the code substituting your own cell reference for D2, which will allow you to make multiple selections in the drop-down list.
Private Sub Worksheet_Change(ByVal Target As Range) Dim xRng As Range Dim xValue1 As String Dim xValue2 As String If Target.Count > 1 Then Exit Sub On Error Resume Next Set xRng = Cells.SpecialCells(xlCellTypeAllValidation) If xRng Is Nothing Then Exit Sub Application.EnableEvents = False If Not Application.Intersect(Target, xRng) Is Nothing Then xValue2 = Target.Value Application.Undo xValue1 = Target.Value Target.Value = xValue2 If xValue1 <> "" Then If xValue2 <> "" Then If xValue1 = xValue2 Or _ InStr(1, xValue1, ", " & xValue2) Or _ InStr(1, xValue1, xValue2 & ",") Then Target.Value = xValue1 Else Target.Value = xValue1 & ", " & xValue2 End If End If End If End If Application.EnableEvents = True End Sub
Simply Press F5 to save and run the code and exit the “VB Editor.”
Now you will be able to make multiple selections within the drop-down list.
Keep in mind that this will only work for a single drop-down list without permitting the repetition of the same element.
You can, however, alter the VBA code slightly to suit your own needs.
For example, if you would like to make multiple drop-down lists on the same column, you could replace:
If Target.Address = “$D$2” Then
With:
If Target.Column = 4 Then
This would allow each list in column D to have the same functionality, and you could add more than one column, such as:
If Target.Column = 4 or Column 5 Then
You can simply adapt the code to suit your needs.
Keep in mind that once you are done, you will need to save your file as .xls or .xlsm to allow macros like the one we just entered.
How To Make Multiple Selections with Repetition
If you would like to create a list that permits multiple selections, including permitting the same selection more than once, you can follow the exact same steps as above.
However, enter the following code substituting the appropriate cell reference instead.
Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$D$2" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = “” Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & ", " & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Simply save and exit the “VB Editor” the same as you would with the other code.
Conclusion
Now you know how to select multiple items from the same drop-down list using the method above.
Whether you need to permit repetition or not, you can use one of the two above codes.
As you can see, this is relatively easy to do, and once you have it set up, it is easy to apply whenever you need to in any workbook.