How to Make Multiple Selections in a Drop Down List in Excel

One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel.

When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.

He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.

Something as shown below in the pic:

There is no way you can do this with Excel in-built features.

The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value.

Watch Video – How to Select Multiple Items from an Excel Drop Down List

How to make Multiple Selections in a Drop Down List

In this tutorial, I will show you how to make multiple selections in an Excel drop-down list (with repetition and without repetition).

This has been one of the most popular Excel tutorials on this site. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. So if you have any questions after reading this, please check out the FAQ section first.

There are two parts to creating a drop-down list that allows multiple selections:

• Creating the drop-down list.
• Adding the VBA code to the back-end.

Creating the Drop Down List in Excel

Here are the steps to create a drop-down list in Excel:

1. Select the cell or range of cells where you want the drop-down list to appear (C2 in this example).

2. Go to Data –> Data Tools –> Data Validation.

3. In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria.

4. In Source field, select the cells which have the items that you want in the drop down.

5. Click OK.

Now, cell C2 has a drop-down list which shows the items names in A2:A6.

As of now, we have a drop-down list where you can select one item at a time (as shown below).

To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end.

The next two sections of this tutorial will give you the VBA code to allow multiple selections in the drop-down list (with and without repetition).

VBA Code to allow Multiple Selections in a Drop-down List (with repetition)

Below is the Excel VBA code that will enable us to select more than one item from the drop-down list (allowing repetitions in selection):

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$C$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

Now you need to place this code in a module in VB Editor (as shown below in the ‘Where to put the VBA code’ section’).

When you have placed this code in the backend (covered later in this tutorial), it will allow you make multiple selections in the drop down (as shown below).

Note that if you select an item more than once, it will be entered again (repetition is allowed).

Try it Yourself.. Download the Example File

VBA Code to allow Multiple Selections in a Drop-down List (without repetition)

A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.

Here is the code that will make sure an item can only be selected once so that there are no repetitions:

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$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
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Now you need to place this code in a module in VB Editor (as shown in the next section of this tutorial).

This code will allow you to select multiple items from the drop-down list. However, you will only be able to select an item only once. If you try and select it again, nothing would happen (as shown below).

Try it Yourself.. Download the Example File

Where to Put the VBA Code

Before you start using this code in excel, you need to put it in the back-end, such that it gets fired whenever there is any change in the drop-down selection.

Follow the below steps to put the VBA code in the backend of Excel:

1. Go to the Developer Tab and click on Visual Basic (you can also use the keyboard shortcut – Alt + F11). This will open the Visual Basic Editor.

2. There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).

3. Double click on Worksheet Name (in the left pane) where the drop-down list resides. This opens the code window for that worksheet.

4. In the code window, copy and paste the above code.

5. Close the VB Editor.

Now when you go back to the drop-down and make selections, it will allow you to make multiple selections (as shown below):

Try it Yourself.. Download the Example File

Note: Since we are using a VBA code to get this done, you need to save the workbook with a .xls or .xlsm extension.

Frequently Asked Questions (FAQs)

I have created this section to answer some of the most asked questions about this tutorial and the VBA code. If you have any questions, I request you to go through this list of queries first.

Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells?

Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code:

If Target.Address = "$C$2" Then

with this line:

If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then

Q: I need to create multiple drop-downs in entire column 'C'. How do I get this for all the cells in the columns with multi-select functionality?

Ans: To enable multiple selections in drop-downs in an entire column, replace the following line in the code:

If Target.Address = "$C$2" Then

with this line:

If Target.Column = 3 Then

On similar lines, if you want this functionality in column C and D, use the below line:

If Target.Column = 3 or Target.Column = 4 Then

Q: I need to create multiple drop-downs in a row. How can I do this?

Ans: If you need to create drop-down lists with multiple selections in a row (let's say the second row), you need to replace the below line of code:

If Target.Address = "$C$2" Then

with this line:

If Target.Row = 2  Then

Similarly, if you want this to work for multiple rows (let's say second and third row), use the below line of code instead:

If Target.Row = 2  or Target.Row = 3 Then

Q: As of now, the multiple selections are separated by a comma. How can I change this to separate these with space (or any other separator).

Ans: To separate these with a separator other than a comma, you need to replace the following line of VBA code:

Target.Value = Oldvalue & ", " & Newvalue

with this line of VBA code:

Target.Value = Oldvalue & " " & Newvalue

Similarly, if you want to change comma with other character, such as |, you can use the following line of code:

Target.Value = Oldvalue & "| " & Newvalue

Q: Can I get each selection in a separate line in the same cell?

Ans: Yes you can. To get this, you need to replace the below line of VBA code:

Target.Value = Oldvalue & ", " & Newvalue

with this line of code:

Target.Value = Oldvalue & vbNewLine & Newvalue

vbNewLine inserts a new line in the same cell. So whenever you make a selection from the drop-down, it will be inserted in a new line.

Q: Can I make the multiple selection functionality work in a protected sheet?

Ans: Yes you can. 

To get this done, you need to do two things:

Add the following line in the code (right after the DIM statement): 

Me.Protect UserInterfaceOnly:=True

Second, you need to make sure the cells - that have the drop-down with multiple selection functionality - are not locked when you protect the entire sheet.

Here is a tutorial on how to do this: Lock Cells in Excel 

You May Also Like the Following Excel Tutorials:

• Creating Multiple Drop-down Lists in Excel without Repetition.
• Display Main and Subcategory in Drop Down List in Excel.
• Create an Excel Drop Down list with Search Suggestions.
• Get Multiple Lookup Values in a Single Cell.
• How to Remove Drop-Down List in Excel?