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.

Download Workbook

Preparation

  1. Start by creating a standard data validation dropdown. The rest will be handled with a VBA macro.
  2. Once the dropdown is ready, press the Alt + F11 keys to open VBA
  3. 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.

How to make multiple selections in a drop down list in Excel 04

Parsing multiple selected values

You can even parse the selected values to consolidate the corresponding data. Check out following example.

How to make multiple selections in a drop down list in Excel 05

You can find detail information about text parsing in our How to split text with formulas in Excel article.