How to Create A MULTI-SELECT Drop-Down List in Excel!
ฝัง
- เผยแพร่เมื่อ 6 ก.พ. 2025
- In this video, I'll walk you through the step-by-step process of setting up a multi-select drop-down list, a technique that can significantly streamline your workflow and reduce the margin for error. Whether you're compiling reports, managing inventories, or tracking projects, this tutorial is designed to make your Excel experience smoother and more efficient. Don't forget to subscribe to Anser's Excel Academy for more tips and tricks that will turn you into an Excel wizard. Together, let's unlock the full potential of Excel and make data management a breeze.
VBA Code credit goes to @SoftTechTutorials89
Private Sub Worksheet_Change(ByVal Target As Range)
' Initializing variables
Dim oldVal As String
Dim newVal As String
' Checking for changes in the drop down lists
Application.EnableEvents = True
If Target.Address = "$G$2" Or Target.Address = "$G$3" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
Exit Sub
' Adding the multiple selected items to the list
Else
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Then
Target.Value = newVal
Else
If InStr(oldVal, newVal) = 0 Then
Target.Value = oldVal & ", " & newVal '& vbNewLine &
Else
Target.Value = oldVal
End If
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
Great tip!!!!!
Thank you!
Excellent video, thank you
Absolutely!@@hichamhadj9640
This is fantastic!! What would be the code to incorporate (1) a range of values, (2) for more than one column or row, (3) where you can remove values. I've tried modifications but nothing is working and it is constantly resetting all of the work I am doing
This is AWESOME!!!! Now that we can select multiple items in the dropdown list, formulate that into multiple cells in one column, and remove a duplicate item...How can all of these wonderful codes be maintained and applied to multiple columns within the same worksheet without disturbing the other columns?
Is there a way that I would be able to filter that column for just Isabella Chang (if I wanted to know which assignments she has)? I can't figure it out with multiple selections.
This is superb. i was in assumption that this can only achieve by adding the activeX or form control in sheet
This is amazing thank you so much! I got the code to work for multi-select as well as updated code to allow for removal. Cannot figure out how to allow for more than one column. I have multiple columns with different data validation lists that need to offer multi-select. Any suggestions?
very helpful ! clear and well explained
thanks for sharing this video - should this work on MacBook Pro excel? I couldn't get it to work...
Thank you so much! this example actualy work!
You're welcome!
Great tip!!! Can you show multiple cells? Can you reference a range of cell? Doing a single cells for 300 cells repetitive seems like alot. Just started doing this so very green at this.
start by copying the data validation rule from the cell where the dropdown is already set up. You can do this by selecting the cell, pressing Ctrl + C, or right-clicking and choosing "Copy." Next, highlight all the rows in the column where you want the dropdown to appear. This can be done by clicking and dragging over the cells or selecting the entire column. Once highlighted, right-click and choose "Paste Special," then select "Validation" to apply the dropdown rules to all the selected cells.
@@ExcelWithAnser I tried exactly what you just described. The standard (single item select) validation copied over, but not the multi-select. Do you have further guidance for multi-select validation on a range of cells?
@MattiaScassellati has a solution in the comment thread below.
To recap:
Replace
If Target.Address = "$G$2" Then
With
If Not Intersect (Target, Range("G1:G300")) Is Nothing Then
This solultion works. The only issue is the error checking still flags if multiple selections are made. The work around would be to deselect the error checking, and do without it.
Is it possible to apply multiple selection drop-down list in multiple columns in a sheet? If so, how?
This is amazing- THANK YOU!
How does it help me of I do not know how to apply this macro to the other cells in that column? Wish there would be more info where and how to work in the developer and how to apply macro to other cells in that column
If I have to put multi- select dropdown from cell A2 to A500, the what will be the VBA code? Pls suggest
How do I delete the extra selections if I accidentally listed too many in the box
First, right-click on the cell with the dropdown, and select Data Validation from the context menu. In the Data Validation dialog box, under the Settings tab, you’ll see the list of options in the Source field. Simply edit this list by removing the extra options or adjusting the range of the source if it's pulling from a range of cells.
This was very useful. Thank you
Great tutorial! Does this VBA run even when the workbook is shared?
I believe so. As long as the file is XLSM, it shouldn't be an issue.
I want to apply that for a whole column, i have various "Tasks" and need multiple people on al lthen anyone know how to make it work, at the moment i can only do 1 Cell.
This works for me, but the issue I see with this is that you can only ADD another value but cannot REMOVE one that you have mistakingly added. If you try this the whole macro thing breaks and stops working completyly (for me on mac)
Hi Daniel,
Yes, because the VBA code is not designed to handle those situations; here's a modified code, notice how the last part is altered:
Private Sub Worksheet_Change(ByVal Target As Range)
' Initializing variables
Dim oldVal As String
Dim newVal As String
' Checking for changes in the drop down lists
Application.EnableEvents = True
If Target.Address = "$G$2" Or Target.Address = "$G$3" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
Exit Sub
Else
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Then
Target.Value = newVal
Else
' If newVal already exists, remove it, otherwise add it
If InStr(oldVal, newVal) = 0 Then
Target.Value = oldVal & ", " & newVal
Else
Target.Value = Replace(oldVal, ", " & newVal, "")
Target.Value = Replace(Target.Value, newVal & ", ", "")
If Target.Value = "" Then Target.Value = newVal
End If
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
@@ExcelWithAnser Thanks a bunch
Hi mate, what if you have Products (not names) in the Table on the left....and have their grams (or price) in the second column? ....and you want not only to multiselect them, but also to calculate (in a separate cell) their weight (or total price)? How you gonna solve this task?
Thanks, Helped
This was great *but* how to get the formula and code to apply to multiple cells in a column? I could tell how to get it into 2 cells by the video, but I have a column of 100 where I need to do this. Hint: dragging down doesn't apply the ability to select multiple of the drop-down options with the comma-separated-value feature.
(Taking the A column as example), substitute
If Traget.Address = "$A$1" Then
with
If Not Intersect (Target, Range("A1:A300")) Is Nothing Then
Then the code will apply for any cell between A1 and A300. It worked for me!
@@MattiaScassellati it for some reason won't do multiple selection no matter what
@@MattiaScassellati Thanks for sharing.
@user-qr2ln6wx6y - Did that solve your problem?
@@MattiaScassellati Thank you!! you saved my sanity
@@MattiaScassellatithis works but then no multiple selection
I took all steps you said but still only one person gets selected from the drop down list and basically the names get overwritten. Is there any solution please?
Hi Sajza, maybe your code is not correct; I have added the VBA code to the video description, so you can copy from there. Also, did you change the target cell in the VBA code and delete the next section, as shown in the video?
@@ExcelWithAnser hi, actually you’re right the code was incorrect. Thanks again.
hello, can someone help me😢I already followed all the steps and checked if somethings wrong with my copy and paste in the code and there's no problem in it, but still it doesn't work
I’m exactly the same, followed the video and still nothing
@@tomwilson2238 me too - nothing
Make the file is .xlsm
Macro enable
Place this code on the sheet that you are working, not on separate module
it is great video, thanks for that. Is there any way that i can apply multi select drop lists across 3 columns (eg A, B, C) and in each column multiple cells (eg A1:A100, B1:B100, C1:C100)?
The problem i have now is when i added the "If Not Intersect (Target, Range("A1:A100")) Is Nothing Then" it work on column A from cell 1 to cell 100, but if I apply the same command to column B it cancel the multi select in column A. Please help
how to put the names in another line but in the same cell?
As we select each time, the drop down list disappears, is there any way to create a form where options are given on screen and users just have to click options
One way to achieve this is by designing an interactive form using checkboxes or buttons. In Excel, you can insert checkboxes by going to the Developer tab (you might need to enable it first via Excel Options) and selecting Insert > Form Controls > Checkbox.
How do we make it so every cell in a column has this same ability?
Yes i need this too, for 1000's of lines! Cant be adding them through "OR ...OR..."
Mine isnt working :/ i ve tried everything
the command of alt plus f11 i have no idea how to do which makes this video useless to me - I'm in another window figuring out your instructions
Hi, not working for me is there possibly add-ins I need, or maybe I don't have that level of the excel program?
Never mind .... I used a lower case $d$3 instead of the capital letter "D$3".
@@kellybrickley313 Great, I'm happy to hear it worked for you!
Could you please write the VBA code in the comments section separately im unable to copy paste it from description
Hi Hina,
Here you go:
Private Sub Worksheet_Change(ByVal Target As Range)
' Initializing variables
Dim oldVal As String
Dim newVal As String
' Checking for changes in the drop down lists
Application.EnableEvents = True
If Target.Address = "$G$2" Or Target.Address = "$G$3" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
Exit Sub
' Adding the multiple selected items to the list
Else
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Then
Target.Value = newVal
Else
If InStr(oldVal, newVal) = 0 Then
Target.Value = oldVal & ", " & newVal '& vbNewLine &
Else
Target.Value = oldVal
End If
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
How to apply to an entire column?
Start by copying the data validation rule from the cell where the dropdown is already set up. You can do this by selecting the cell, pressing Ctrl + C, or right-clicking and choosing "Copy." Next, highlight all the rows in the column where you want the dropdown to appear. This can be done by clicking and dragging over the cells or selecting the entire column. Once highlighted, right-click and choose "Paste Special," then select "Validation" to apply the dropdown rules to all the selected cells.
where is the code?
i followed the steps as it but it dosent work with me.what shall i do?!
Hi there, make sure your macros are enabled (saving the file as XLSM), and that you have the correct cell references in your VBA code; Also, it may be a good idea to remove any existing formatting that was done to the cells.
how do you remove the values
Hi Jose,
The VBA code is not designed to handle those situations; here's a modified code, notice how the last part is altered:
Private Sub Worksheet_Change(ByVal Target As Range)
' Initializing variables
Dim oldVal As String
Dim newVal As String
' Checking for changes in the drop down lists
Application.EnableEvents = True
If Target.Address = "$G$2" Or Target.Address = "$G$3" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
Exit Sub
Else
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Then
Target.Value = newVal
Else
' If newVal already exists, remove it, otherwise add it
If InStr(oldVal, newVal) = 0 Then
Target.Value = oldVal & ", " & newVal
Else
Target.Value = Replace(oldVal, ", " & newVal, "")
Target.Value = Replace(Target.Value, newVal & ", ", "")
If Target.Value = "" Then Target.Value = newVal
End If
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
@@ExcelWithAnser Hi, is there a Code for Deselecting, I mean if I click the same value then it should remove the value. I could not find the code for the same in any of the comments :(
@@amarm3532 if you combine the previous answers from @ExcelWithAnser and @MattiaScassellati you get a code for MULTIPLE CELLS in a column and can REMOVE the values (chosen by mistake). If your first cell in the list of choices is blank, you can quickly deselect all and start again.
But this code do not solve this issue: if your list of choices contains multi-word-strings ( for example cell A2= coffee; A3= coffee black; A4= coffee with milk) if you select "coffee" from dropdown list, you get "coffee". then if you select "coffee black", you get "coffee, coffee black", then if you select "coffee with milk" you get "coffee, coffee black, coffee with milk" but then if you deselect "coffee", you get "coffee black with milk" (this is why a blank cell is a good idea). Can anybody help to solve this issue?
Private Sub Worksheet_Change(ByVal Target As Range)
' Initializing variables
Dim oldVal As String
Dim newVal As String
' Checking for changes in the drop down lists
Application.EnableEvents = True
If Not Intersect(Target, Range("$B$2:$B$100")) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
Exit Sub
Else
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Then
Target.Value = newVal
Else
' If newVal already exists, remove it, otherwise add it
If InStr(oldVal, newVal) = 0 Then
Target.Value = oldVal & ", " & newVal
Else
Target.Value = Replace(oldVal, ", " & newVal, "")
Target.Value = Replace(Target.Value, newVal & ", ", "")
If Target.Value = "" Then Target.Value = newVal
End If
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
How to unselect the value which is already selected previously and recorded in cell but now want remove 2 or 3 values which is already selected
Did you find ?
How can it be applied to multiple cells? only applicable for 2 cells
This is what I used
Private Sub Worksheet_Change(ByVal Target As Range)
' Initializing variables
Dim oldVal As String
Dim newVal As String
' Checking for changes in the drop down lists
Application.EnableEvents = True
If Not Intersect(Target, Range("$E$2:$E$65")) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
Exit Sub
' Adding the multiple selected items to the list
Else
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Then
Target.Value = newVal
Else
If InStr(oldVal, newVal) = 0 Then
Target.Value = oldVal & ", " & newVal '& vbNewLine &
Else
Target.Value = oldVal
End If
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
@@AlanGabrielDiaz-qt1eq Thank you! This was a big help!
What if you need this functionality for multiple rows in the Column D? Yes I got it working for the first row ($D$2) but all of the rows below it won't work because the code is locked to $D$2, but I need it to go to the next row which is $D$3, $D$4 all the way down to 200+ rows in the D column. Basically I'm trying to replicate this multiple selection but for 200+ rows. I don't want to have to create the VB script 200 times. This is super cool and I bet I'm super close. Btw this is amazing code, thank you so much for creating this video. Really appreciate it.
I had the same issue. This code worked for me:
If Not Intersect(Target, Range("D2:D" & Rows.Count)) Is Nothing Then
@@aidanmarler where did you insert this part of the code? Is this used instead of "If Target.Address = "$G$2" Or Target.Address = "$G$3" Then"
I got it to work with this: If Not Intersect(Target, Range("D1:D300")) Is Nothing Then
Start by copying the data validation rule from the cell where the dropdown is already set up. You can do this by selecting the cell, pressing Ctrl + C, or right-clicking and choosing "Copy." Next, highlight all the rows in the column where you want the dropdown to appear. This can be done by clicking and dragging over the cells or selecting the entire column. Once highlighted, right-click and choose "Paste Special," then select "Validation" to apply the dropdown rules to all the selected cells.
I've made this VBA code for multiple selections from a dropdown list in an Excel worksheet, in this example we use ranges within AA4:AA300. see comment
Private Sub Worksheet_Change(ByVal Target As Range)
' Initializing variables
Dim oldVal As String
Dim newVal As String
' Avoid running the event code recursively
Application.EnableEvents = False
' Checking if the changed cell is within the specified range
If Not Intersect(Target, Range("AA4:AA300")) Is Nothing Then
' Checking if the cell has data validation
If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
' If the cell is not empty
If Target.Value "" Then
newVal = Target.Value
' Undo the recent change to get the old value
Application.Undo
oldVal = Target.Value
' Combine the old and new values, if the new value is not already in the old value
If oldVal = "" Then
Target.Value = newVal
Else
If InStr(1, oldVal, newVal, vbTextCompare) = 0 Then
Target.Value = oldVal & ", " & newVal
Else
Target.Value = oldVal
End If
End If
End If
End If
End If
' Re-enable events
Application.EnableEvents = True
End Sub
As some have mentioned doesn't appear to work. Lame. :(
I appreciate your feedback!
Your bumper music is whack. Also, this works for a single cell. You can't copy this to another cell and have it work. Now I need to make the target a variable.
Use this: If Target.Address = ActiveCell.Address Now you can copy and paste within a sheet, and do this trick all day long. Copy the entire code for each sheet.