Home > Validation > Drop Downs > Multiple Selection

Excel Data Validation - Select Multiple Items

Instead of limiting an Excel data validation drop down list to a single selection, you can use this macro to allow multiple selections. Video and written instructions, and get the free workbook to see how it works.

Multiple Item Selection Articles

This is the main page for the data validation multiple selection technique. I have also written several blog articles, and you can join in the discussion in the comments there:

• Select Multiple Items from Excel Data Validation List
• Edit Multiple Selections in Excel Drop Down Lists
• How to Set up Multiple Selection Excel Drop Down
• Excel Drop Down Multi-Select - Add Sort
• No Duplicates in Multiple Selection Excel Drop Down
• Excel Drop Down Multiple Select or Remove
• Enter Multiple Items in a Non-Data-Validation Cell
• Multiple Selection Drop Down With Codes
• Data Validation - Select Multiple Items From Listbox
• Select Cell Items From ListBox
• Show Data Validation Items in List Box

Video Multiple Item Selection Examples

To see how this technique works, and a few multiple selection examples, you can watch this short video.

Select Items With Drop Down List

To make data entry easier, you can add an Excel data validation drop down list to your worksheet. Then, click the arrow, and select an item to fill the cell.

In the worksheet shown below, you can select any month from the drop down list, and it overwrites any previous entry in the cell.

Select Multiple Items From Drop Down List

Instead of limiting the drop down list to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.

With a few adjustments to the VBA code, you can display all the selected items across a row, or down a column, or keep them in a single cell.

Fill Across a Row

In the first example, each item selected in this dropdown list fills the next empty cell at the right.

With this option, you could fill player names across a list of innings, or employee names for days of the week.

Fill Down a Column

Another option is to fill the selected items down a column, instead of across the row.

Add Values To the Same Cell

With another variation in the VBA code, you can keep all the selected items in the same cell, separated by commas.

View Code for Select Multiple Items

The sample workbook uses Microsoft Excel VBA code (Visual Basic for Applications), which is set up to run automatically, when you make a change on the worksheet.

To see the code for any sheet in the sample file, you can right-click the sheet tab, and click on View Code.

Then, follow the examples shown below, if you want to make minor changes to the code.

Change Column Number

In the sample code shown below, column 3 is the only one where the change will occur. In the screen shot, the line is marked with a red dot:

If Target.Column = 3 Then

In your workbook, you could change the 3 to a different number.

If Target.Column = 6 Then

Or, add more columns in the code. For example:

If Target.Column = 3 _
Or Target.Column = 5 _
Or Target.Column = 6 Then

Or, use a starting and ending column. For example:

  If Target.Column >= 3 And _
Target.Column <= 10 Then

Or, include a starting row as well. For example:

If Target.Column = 3 _
And Target.Row >= 4 Then

Delete Code Lines

Or, if you don't want to limit the code to a specific column, you could delete the two rows that are marked with a red dot. When those lines are removed, the code will run when any cell with a drop down list is selected.

Change Sheet and Range Names

In some code samples, the sheet names and named ranges are included. You will have to change these to match your sheet names and range names.

For example, shown below is a portion of the code from the LineBreakAddSort sheet. The sheet and range names are highlighted in yellow.

Also, this code is set up for a list in column 3.

i=ws.Cells(Rows.Count,3).End(xlUp).Row + 1

You would need to change that number, if your list is in a different column.

Video: Multiple Item Selection Setup

To see the steps for setting up data validation with multiple selection, watch this short video tutorial

Allow Editing With Multiple Selections

With this technique, you can select multiple items from a drop down list, but if you try to edit the cell, you might see a data validation error alert, or find that the VBA code simply added your revised text, to the existing text -- probably not the result that you wanted!

Edit Multiple Selection Drop Down List

In the sample file, on the SameCellEdit worksheet, there is an "Edit Entries" check box. Check that box, and you can edit the cell, without any problems.

To edit a cell with multiple items selected:

• Add a check mark in the Edit Entries check box
• Select the cell, and edit the entries, either in the cell, or in the formula bar

• Press Enter, to complete the editing

Return to Multiple Selection

After you finish editing the cell(s), press Enter, to complete the editing. Then, remove the check mark from the Edit Entries check box.

Add an Editing Check Box

To add a check box on your worksheet:

• On the Excel Ribbon's Developer tab, click Insert, and then click the Check Box under Form Controls

• Click at the top of the worksheet, to add a check box

• Select the default text, and type "Edit Entries"

• Somewhere in the workbook, create a cell named "EditMode". In this example, the EditMode cell is on a worksheet named AdminNotes.

• Right-click on the check box, and click Format Control

• On the Control tab, enter EditMode as the Cell link, and click OK.

The Revised Multiple Selection VBA Code

In the sample file, you can see the code that allows editing when the Edit Entries box is checked. In the screen shot shown below, the EditMode range is set as a variable (rngEdit), and the code looks at that cell's value.

• If the value is False (no check mark), the multiple selection code runs.
• If the value is True (check mark), the multiple selection code does not run.

Allow New Items in List - Add Sort

On the SameCellAddSort sheet in the sample file, there is an example where you can add new items to the drop down lists, but only in specific columns (C and D).

The new items are added to the Lists worksheet, and then the list is sorted A-Z.

To see this example, and how the Excel VBA code works, watch this short video tutorial

Video Timeline

• 0:00 Data Validation Drop Downs
• 0:37 Add New Item to Names List
• 1:15 See Excel VBA Code & Lists
• 2:03 Step Through VBA Code
• 4:33 Code Adds New Item to List

No Duplicates in Cell

On the SameCellNoDups sheet, there is an example where you are not allowed to select the same item twice in any of the cell.

• If the item is not currently in the cell, it is added to the active cell in column C, separated by a comma
• If the item has been previously selected in the cell, that item is not added again.

On the SameCellNoDupsCol sheet, duplicate selections are allowed in cells in columns B and D, but no duplicates can be entered in the cells in column C.

Remove Previous Selections From Cell

On the SameCellAddRemove worksheet, there is an example that lets you remove items if they been previously selected. For example, the cell contains four items -- "One, Two, Three, Four".

When you select Three again, that number is removed from the cell, instead of being added at the end of the cell.

Video: Remove Previous Selections From Cell

This video shows how the multiple select and remove features work. You can also see the Excel VBA code, and the steps that it goes through, when you make a selection in the drop down list.

Multiple Selection Sample Code

The following code samples should be copied onto the worksheet module where you want to use the multiple selection technique. This code adds multiple items in the same cell, separated by a comma.

There are many more code examples in the sample workbook that you can download below.

NOTE: This code does not work if the sheet is protected.

Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 3 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      Target.Value = oldVal _
        & ", " & newVal
'      NOTE: you can use a line break,
'      instead of a comma
'      Target.Value = oldVal _
'        & Chr(10) & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

Code on Protected Worksheet

If the worksheet is protected, the Cells.SpecialCells(xlCellTypeAllValidation) code will not work. Instead, you can check the .Validation.Type property of the target cell, to see if it contains a drop down list.

This example is on the SameCellProtect sheet in the sample file. It is set up to work on column 3 only.

NOTE: The Premium Version also works on protected worksheets.

Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
'code runs on protected sheet
Dim oldVal As String
Dim newVal As String
Dim strSep As String
Dim strType As Long

'add comma and space between items
strSep = ", "

If Target.Count > 1 Then GoTo exitHandler

'checks validation type of target cell
'type 3 is a drop down list
On Error Resume Next
strType = Target.Validation.Type

If Target.Column = 3 And strType = 3 Then
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  If oldVal = "" Or newVal = "" Then
    Target.Value = newVal
  Else
    Target.Value = oldVal _
      & strSep & newVal
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

AutoFilter With Multiple Selections

If you enter multiple items in a cell, you will see each unique combination of items in the AutoFilter drop downs. That can make it difficult to filter for a specific item, such as "Two".

As a workaround, you can use one of the techniques shown below:

1. Use the Search box to find items that contain specific text
2. Use a Slicer to filter for specific text.

Search Box - Contains

In Excel 2010 and later, type in the filter Search box, just above the list of filter options. The list is automatically filtered as you type, to show only the items that contain the text that has been entered in the Search box..

Filter with Slicer (Macro)

In this example, there are multiple weekday names selected in the WorkDays column. Above the table, click on a Slicer, to filter the WorkDays column for any rows that contain the selected weekday name.

In the sample file, code runs automatically, to allow multiple selection, and and to filter the WorkDays column.

You can get this sample file in the Download section, below, and there are notes in the Excel file, on how this technique is set up.

Download the Sample Data Validation File

Multiple Selection Example: To experiment with the multiple selection technique, you can download the zipped sample file: Select Multiple Items from Excel Data Validation List. If you copy the code into a different workbook, remember to check the code for sheet names, range names, row numbers and column numbers, and adjust those if necessary.

Filter With Slicer: To test the Filter with Slicer technique, you can download the zipped sample file: Filter Data in Multi-Select Cells. The zipped Excel file contains macros, so be sure to enable macros if you want to test the code.