How to Create Drop Down list in Excel with Multiple Selections

👉How to Create Drop Down list in Excel with Multiple Selections

Certainly! Creating a multi-select drop-down list in Excel allows users to choose multiple items from a predefined list. This feature can be incredibly useful for data input and accuracy. Let’s walk through the steps to create such a drop-down list:

👉Create a Normal Drop-Down List:

First, let’s create a regular data validation list in one or more cells. We’ll use this as the basis for our multi-select drop-down.

Here’s how to do it:

• Select the cell where you want the drop-down list to appear.
• Go to the “Data” tab on the ribbon.
• Click “Data Validation” in the “Data Tools” group.
• In the “Allow” drop-down menu, select “List”.
• In the “Source” box, enter the range of cells for your list (e.g., A2:A25).
• Click “OK”.

➡️Insert VBA Code for Multiple Selections:

• Now comes the magic! We’ll use VBA (Visual Basic for Applications) to enable multiple selections.
• Insert one of the following codes at the back end of your target worksheet:

➡️Multi-select drop-down without duplicates:

Private Sub Worksheet_Change(ByVal Target As Range)

1.     Dim rng As Range
2.     Dim cell As Range
3.     Dim selectedItems As String
4.     
5.     ' Define the range for your drop-down list
6.     Set rng = Me.Range("D3:D7")
7.     
8.     If Not Intersect(Target, rng) Is Nothing Then
9.         For Each cell In rng
10.             If cell.Value = True Then
11.                 selectedItems = selectedItems & cell.Value & ", "
12.             End If
13.         Next cell
14.         
15.         ' Remove trailing comma and space
16.         selectedItems = Left(selectedItems, Len(selectedItems) - 2)
17.         Target.Value = selectedItems
18.     End If
19. End Sub

➡️Multiple selection drop-down with custom delimiter:

Private Sub Worksheet_Change(ByVal Target As Range)

1.     Dim rng As Range
2.     Dim cell As Range
3.     Dim selectedItems As String
4.     Dim delimiter As String
5.     
6.     ' Define the range for your drop-down list
7.     Set rng = Me.Range("D3:D7")
8.     delimiter = "; " ' Customize the delimiter as needed
9.     
10.     If Not Intersect(Target, rng) Is Nothing Then
11.         For Each cell In rng
12.             If cell.Value = True Then
13.                 selectedItems = selectedItems & cell.Value & delimiter
14.             End If
15.         Next cell
16.         
17.         ' Remove trailing delimiter
18.         selectedItems = Left(selectedItems, Len(selectedItems) - Len(delimiter))
19.         Target.Value = selectedItems
20.     End If
21. End Sub

➡️Multiple selections in separate lines:

Private Sub Worksheet_Change(ByVal Target As Range)

1.     Dim rng As Range
2.     Dim cell As Range
3.     Dim selectedItems As String
4.     
5.     ' Define the range for your drop-down list
6.     Set rng = Me.Range("D3:D7")
7.     
8.     If Not Intersect(Target, rng) Is Nothing Then
9.         For Each cell In rng
10.             If cell.Value = True Then
11.                 selectedItems = selectedItems & cell.Value & vbNewLine
12.             End If
13.         Next cell
14.         
15.         ' Remove trailing newline
16.         selectedItems = Left(selectedItems, Len(selectedItems) - Len(vbNewLine))
17.         Target.Value = selectedItems
18.     End If
19. End Sub

➡️Test Your Multi-Select Drop-Down:

• Save your workbook and test the drop-down list. You’ll now be able to select multiple items!
• Remember that VBA macros need to be enabled for this to work.

And there you have it! A multi-select drop-down list in Excel that allows users to choose multiple items with ease. 📊 For more details and examples, you can refer to this article1. Happy Excel-ing! 🚀