Excel doesn’t have any built-in feature that allows you select multiple items in a dropdown. In this guide, we're going to show you how to make multiple selections in a dropdown list in Excel.
Preparation
- Start by creating a standard data validation dropdown. The rest will be handled with a VBA macro.
- Once the dropdown is ready, press the Alt + F11 keys to open VBA
- Double-click on the sheet item on the Project pane to the left. This will open the corresponding editor on the right.
The code should be in the worksheet’s editor containing the dropdown, because the code tracks changes in the cell.
VBA Code to make multiple selections in a dropdown list
All you need to do is to copy and paste the following code into your file and change the cell reference of the dropdown.
Private Sub Worksheet_Change(ByVal Target As Range)
'Define variables
Dim ExistingValue As String
Dim NewValue As String
Dim Separator As String
'If an error occurs, enable events and quit the code
On Error GoTo Quit
'Check if the dropdown cell is changed
If Target.Address = "$F$2" Then
'If user deletes the dropdown cell's data do nothing
If Target.Value = "" Then GoTo Quit
'The fun begins
Application.EnableEvents = False
NewValue = Target.Value
Application.Undo
ExistingValue = Target.Value
If ExistingValue = "" Then
Target.Value = NewValue
Else
'Check if the dropdown item is already selected
If InStr(1, ExistingValue, NewValue) = 0 Then
Target.Value = ExistingValue & ", " & NewValue
Else
Target.Value = ExistingValue
End If
End If
End If
Quit:
Application.EnableEvents = True
End Sub
The dropdown cell’s address is on the 9th row of the code. If you are using a named range, replace the cell address “$F$4” with Range(<named range>).Address. To learn more about the cell referencing in VBA, check out How to refer a range or a cell in Excel VBA.
Check the 22nd line if you want to use another character as a separator and replace the comma with any character you want.
Parsing multiple selected values
You can even parse the selected values to consolidate the corresponding data. Check out following example.
You can find detail information about text parsing in our How to split text with formulas in Excel article.