Select multiple items in Excel drop-down list – full guide
Excel drop-down lists are a fantastic tool for ensuring data consistency and ease of entry. However, by default, they restrict you to selecting just one item. But what if you need to select multiple items from the same drop-down list? This comprehensive guide will explore methods to enable multiple selections in Excel drop-down lists, manage duplicates, set custom separators, and define the scope of these lists.
- Allowing duplicate items
- Removing any existing items
- Setting a custom separator
- Setting a specified range
- Executing in a protected worksheet
Enabling Multiple Selections in Drop-Down List
This section provides two methods to help you enable multiple selections in drop-down list in Excel.
Using VBA Code
To allow multiple selections in drop-down list, you can use Visual Basic for Applications (VBA) in Excel. The script can modify the behavior of a drop-down list to make it a multiple-choice list. Please do as follows.
Step 1: Open the Sheet (Code) editor
- Open the worksheet that contains the drop-down list for which you want to enable multiple selection.
- Right click the sheet tab and select View Code from the context menu.
Step 2: Use VBA code
Now copy the following VBA code and paste it to the opening sheet (Code) window.
VBA code: Enable multiple selections in Excel drop-down list.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Result
When you return to the worksheet, the drop-down list will enable you to choose multiple options,see the demo below:
The above VBA code:
- Applies to all data validation drop-down lists in the current worksheet, both existing and those created in the future.
- Prevents you from picking the same item more than once in each drop-down list.
- Uses comma as the separator for the selected items. To use other delimiters, please view this section to change the separator.
Using Kutools for Excel in a few clicks
If you're not comfortable with VBA, an easier alternative is Kutools for Excel's Multi-select Drop-down List feature. This user-friendly tool simplifies enabling multiple selections in drop-down lists, allowing you to customize the separator and manage duplicates effortlessly to meet your different needs.
After installing Kutools for Excel, go to the Kutools tab, select Drop-down list > Multi-select Drop-down List. Then you need to configure as follows.
- Specify the range containing the drop-down list from which you need to select multiple items.
- Specify the separator for the selected items in the drop-down list cell.
- Click OK to complete the settings.
Result
Now, when you click on a cell with a drop-down list in the specified range, a list box will appear next to it. Simply click the "+" button next to the items to add them to the drop-down cell, and click the "-" button to remove any items you don't want anymore. See the demo below:
- Check the Wrap Text After Inserting a Separator option if you want to display the selected items vertically within the cell. If you prefer a horizontal listing, leave this option unchecked.
- Check the Enable search option if you want to add a search bar to your drop-down list.
- To apply this feature, please download and install Kutools for Excel first.
More operations for multi-selection drop-down list
This section collects the different scenarios that may be required when enabling multiple selections in the Data Validation drop-down list.
Allowing duplicate items in drop-down list
Duplicates can be a problem when multiple selections are allowed in a drop-down list. The VBA code above does not allow duplicate items in the drop-down list. If you need to keep duplicate items, try the VBA code in this section.
VBA code: Allow duplicates in data validation drop-down list
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
Target.Value = xValue1 & delimiter & xValue2
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Result
Now you can select multiple items from the drop-down lists in the current worksheet. To repeat an item in a drop-down list cell, continue to select that item from the list. See screenshot:
Removing any existing items from drop-down list
After selecting multiple items from a drop-down list, you may sometimes need to remove an existing item from the drop-down list cell. This section provides another piece of VBA code to help you accomplish this task.
VBA code: Remove any existing items from the drop-down list cell
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRngDV As Range
Dim TargetRange As Range
Dim oldValue As String
Dim newValue As String
Dim delimiter As String
Dim allValues As Variant
Dim valueExists As Boolean
Dim i As Long
Dim cleanedValue As String
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Set your desired delimiter here
If Target.CountLarge > 1 Then Exit Sub
' Check if the change is within the specific range
If Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
If xRngDV Is Nothing Or Target.Value = "" Then
' Skip if there's no data validation or if the cell is cleared
Application.EnableEvents = True
Exit Sub
End If
On Error GoTo 0
If Not Intersect(Target, xRngDV) Is Nothing Then
Application.EnableEvents = False
newValue = Target.Value
Application.Undo
oldValue = Target.Value
Target.Value = newValue
' Split the old value by delimiter and check if new value already exists
allValues = Split(oldValue, delimiter)
valueExists = False
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) = newValue Then
valueExists = True
Exit For
End If
Next i
' Add or remove value based on its existence
If valueExists Then
' Remove the value
cleanedValue = ""
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) <> newValue Then
If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
cleanedValue = cleanedValue & Trim(allValues(i))
End If
Next i
Target.Value = cleanedValue
Else
' Add the value
If oldValue <> "" Then
Target.Value = oldValue & delimiter & newValue
Else
Target.Value = newValue
End If
End If
Application.EnableEvents = True
End If
End Sub
Result
This VBA code allows you to select multiple items from a drop-down list and easily remove any item you've already chosen. After selecting multiple items, if you want to remove a specific one, simply select it again from the list.
Setting a custom separator
The delimiter is set as comma in the above VBA codes. You can modify this variable to any preferred character to use as the separator for the drop-down list selections. Here is how you can do:
As you can see that the above VBA codes all have the following line:
delimiter = ", "
You just need to change the comma to any separator as you need. For example, you want to separate the items by semicolon, change the line to:
delimiter = "; "
delimiter = vbNewLine
Setting a specified range
The above VBA codes apply to all drop-down lists in the current worksheet. If you only want the VBA codes to apply to a certain range of drop-down lists, you can specify the range in the above VBA code as follows.
As you can see that the above VBA codes all have the following line:
Set TargetRange = Me.UsedRange
You just need to change the line to:
Set TargetRange = Me.Range("C2:C10")
Executing in a protected worksheet
Imagine that you have protected a worksheet with the password "123" and set the drop-down list cells to "Unlocked" before activating the protection, thus ensuring that the multi-select function remains active after the protection. However, the VBA codes mentioned above cannot work in this case, and this section describes another VBA script that is specifically designed to handle multi-select functionality in a protected worksheet.
VBA code: Enable multiple selection in drop-down list without duplicates
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Dim isProtected As Boolean
Dim pswd As Variant
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
' Check if sheet is protected
isProtected = Me.ProtectContents
If isProtected Then
' If protected, temporarily unprotect. Adjust or remove the password as needed.
pswd = "yourPassword" ' Change or remove this as needed
Me.Unprotect Password:=pswd
End If
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then
If isProtected Then Me.Protect Password:=pswd
Exit Sub
End If
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
' Re-protect the sheet if it was protected
If isProtected Then
Me.Protect Password:=pswd
End If
End Sub
By enabling multiple selections in Excel drop-down lists, you can greatly enhance the functionality and flexibility of your worksheets. Whether you're comfortable with VBA coding or prefer a more straightforward solution like Kutools, you now have the capability to transform your standard drop-down lists into dynamic, multi-select tools. With these skills, you're now equipped to create more dynamic and user-friendly Excel documents. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.
Related Articles
Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial provides the method to solve the problem.
Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.
Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.
Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Table of contents
- Enabling multiple selections
- Using VBA code
- Using Kutools for Excel in a few clicks
- More operations
- Allowing duplicate items
- Removing any existing items
- Setting a custom separator
- Setting a specified range
- Executing in a protected worksheet
- Related Articles
- The Best Office Productivity Tools
- Comments