You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
Hi Debra,
Great information! Worked perfectly! I had another question that I see a few other readers had asked related to multiple selects but I did not see an answer. Maybe it is not possible to do.
For example, if column A has the following cells:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
Is there some way to set the filter options to each item (e.g. apple or orange or grape) and not the string of items (e.g. apple, orange, grape or orange, apple or grape)? So if I want to filter and show only “orange”, I will get A2 and A3. If I filter to show only “grape”, I will get A2 and A4.
Right now the auto filter shows the following options:
“apple, orange, grape”
“orange, apple”
“grape”
Is this possible?
Thank you!
Bonnie
@Bonnie, in Excel 2010 and later, if you start typing in the Filter’s Search box, the list will be automatically filtered to show only the rows that contain the text you typed.
Hi,
i need below formula how i can use this formula.
=RIGHT(F111
=Left(F111
Hello – I am wondering if there is any way to use the multi select VB code to enable the user to select multiple items from the drop down list using the CTRL key? I added this mutli select capability to a worksheet and my boss is disappointed that users can’t select multiple items at once using CTRL.
@Katheryn, your boss might prefer to use the Listbox version instead:
http://www.contextures.com/excel-data-validation-listbox.html
This has been very helpful, but I really need to just have the multiple selection separated by commas in just one cell, not a whole column. I used the code for “SameCellAddRemove” and it was working great until I realized it was affecting all the other cells in a column that can’t have the comma thing happening to it every time I type in them
This is what I currently have in my spreadsheet that I need to now refer to Columns 6 & 8 AND only Row 2!
Option Explicit
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
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
Target.Value = newVal
Select Case Target.Column
Case 6, 8
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) – 2)
Else
Target.Value = Replace(oldVal, newVal & “, “, “”)
End If
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
Case Else
‘do nothing
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
Just below this line:
add a line of code to check the row number:
This does not work for me at all. I have the code entered exactly, but can still only select a single item. Help! (I’m on a MAC)
@Brian, I don’t have a Mac, so can’t test on that system.
Do you have macros enabled, when you open the workbook?
Debra,
I’m Using SameCell VB and just wondered of I could have my data validation list on another worksheet to keep my lists clean and away from the main grid. Is that possible?