Excel Drop Down Multiple Select or Remove

Excel Drop Down Multiple Select or Remove

There's a popular sample file on my website, that lets you select multiple items from a data validation drop down list. Since the original article, I've posted updates:

And here is the latest update.

Remove Previous Selections From Cell

In the comments on the original post, Dan asked for a way to 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.


Download the Sample File

To experiment with this technique, you can download the sample file from the Contextures website, on the Excel Data Validation - Select Multiple Items page. The file is in Excel 2003 format and zipped. There are macros in the file so enable them to test the features.

The new sample is on the SameCellAddRemove worksheet.

Please let me know in the comments if there are other features you'd like to see in this workbook. Thanks!

Buy the Premium Version

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. Instead of selecting from the drop down, a list box appears when you click the cell.

It automatically selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.

The premium version works with dependent lists too, and runs on a protected worksheet. There is also an option of showing a multi-select listbox, or a single-select version (this is helpful when working with dependent lists -- you don't want multiple items selected in the main columns).

The kit has 3 sample files, and a user guide, with details and screen shots, on how to add this technique to your own workbooks. Click here for details: Data Validation Multi Select Premium.

Watch the Excel Tutorial Video

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.


40 thoughts on “Excel Drop Down Multiple Select or Remove”

  1. Hi -Is it possible to have each menu item when selected appear on a separate line instead of separated by a comma
    Thanks in advance,

  2. I have a quick question...I found out finally how to be able to add more than one selection to the cell, thank you very much for your help. However I have one last question about removing if you accidentally hit a selection that you do not want in that cell? So my question is even after watching the video, I am having difficulties. Can I combine all these codes into one worksheet that I need all these things to happen:
    Adding more than 1 selection
    Removing any additional selections I might have chosen.
    How do I go about that adding all these codes into one worksheet, or do I need to keep them separate on their own worksheet, but then how do I get all these things to work on a spreadsheet column? Please help. Thank you so much.

  3. How could I make the font style of the 1st selection to be (Bold) ” One, Three, Four” (one)to be Bold in this example . What is the suitable code for this to be added with the multiple selection drop down list code?

  4. Also, what if i have more then two cells with drop down menu and i want that all my cells get a multiple selection too?

  5. I am unable to get your workbook to work properly, specifically the SameCellEdit, but nothing appears to work.

  6. I am an old hand at Excel and VBA and doing multi-select picklists is not something I would generally do in Excel. But sometimes Excel is the only tool at hand and it is all about imagining how it can be done. Worked like a champ; even handled the old multi-select data I downloaded from Salesforce.

  7. Your tutorial sessions are fantastic.
    Just wondered do you know how to add more than 1 SameCellAddRemove columns on 1 worksheet.
    I need 3 columns with this samecell add remove feature on same worksheet/

  8. This formula works brilliantly, thanks. Now my only problem is that it doesn't work if the worksheet is protected, even if the actual cell containing the dropdown list is unlocked. It just behaves like a normal data validation list, i.e. the code is ignored.
    Is there any way to allow it to work even if the sheet is protected?

  9. Found the answer! Had to add this macro into ThisWorkbook:
    Private Sub Workbook_Open()
    ActiveSheet.Protect Password:="", UserInterfaceOnly:=True
    End Sub

    1. Hey Edwin,
      Thanks for the code. I'm still new to vba, I cannot get the password unprotect and protect to work - can you share your code please, or how do I incorporate this code into the multiple drop down code?
      Thank you.

  10. This formula/code works great, except when the list of items for the drop down contains common text across more than one item (e.g. list of items contains "Agree", "Strongly Agree", "Disagree", Strongly Disagree").
    In this example, after selecting "Agree" and "Strongly Agree", the cell where the drop down list exists is populated with "Agree, Strongly Agree". However, when re-selecting "Agree" again to remove it (unselect it, if you will), the cell is left with "Agree, Strongl".
    Is there a way to get around this (unfortunately the options have to be the text listed above)?

    1. @Ken, I've added a new worksheet -- SameCellAddRemove2 -- in the sample file, and it avoids the problem of removing similar entries.
      The premium version of the technique also avoids that problem, by showing a list of the selected items. Just remove a check mark, to remove that item.

  11. @Debra, thank you very much for this "tool". I downloaded the sample excel sheet and I was wondering how can I merge the code for "SameCellAddRemove" and "LineBreak". I would like to use both functionalities.

    1. Rodrigo, in the code for the SameCellAddRemove, change the ", " separator to vbCrLf. There are 2 lines where that occurs:
      Target.Value = Replace(oldVal, newVal & vbCrLf, "")
      Target.Value = oldVal _
      & vbCrLf & newVal

  12. Hello there,
    I'm using the code below and it works well. The code applies to the whole worksheet as I have multiple drop downs that I want it to work on. The only issue I have is that I would like to remove the ability to select one particular word on the drop down list alongside any of the other options. In other words, by selecting another choice this word no longer appears in the cell. (The word is "Select"). Many thanks for your help.
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Developed by Contextures Inc.
    ' http://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
    Application.EnableEvents = False
    newVal = Target.Value
    oldVal = Target.Value
    Target.Value = newVal
    If oldVal = "" Then
    'do nothing
    If newVal = "" Then
    'do nothing
    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
    Application.EnableEvents = True
    End Sub

    1. That's surprising. I would have expected the code

      If rngDV Is Nothing Then GoTo exitHandler
      If Intersect(Target, rngDV) Is Nothing Then
         'do nothing
         //do stuff
      End If

      to only respond to that column. Confirm that your code has not been altered.
      Can you post your error details here? (Err.description and Err.Number)

  13. As with Edwin's request, I'm trying to get this code to work on a password protected sheet. I am not getting it right with the code he provided - please help!!
    Thank you.

  14. @Debra Thank you very much for the code. I'm now using it all over. I do have a couple questions though.
    1) After the values are changed to "Choice1, Choice2, Choice3", I'm getting a data validation error. Naturally, the dropdown has three individual choices, neither is "Choice1, Choice2, Choice3". Must I suffer through this error or is there a way around it.
    2) If I type in an item into the box, it will receive it as if I selected it with the mouse. It is case-insensitive though and I may end up with "Choice1, Choice2, choice3" or even "Choice1, Choice2, Choice3, choice3". Do you have a workaround for this.
    3) If I start to type in something and then click the dropdown arrow expecting to select an item, excel immediately tries to validate my partial entry and complains "Invalid entry, [Retry], [Cancel]. If I click [Retry], fine. If I click [Cancel] I get a runtime error "Method Undo Failed". Is there some code that can avoid this issue?
    I thank you again for sharing your expertise with the rest of us.

  15. How do I use SameCellAddRemove code for more than one column? Ex. I currently have it being used for column 12 but I'd also like to apply the multiselection to column 10.

  16. I am currently using the code that allows multiple selections in a cell. However, I am having an issue with the code. Whenever there is data selected in the cell and I double-click in it and then click out of it, whatever was in the cell disappears. This is a huge issue since I cannot undo the deletion of the data in the cell, and there is no other way to recover the data. How can I fix this?
    Please help! Very urgent.

  17. Debra, I am new at this and would like to combine several of these for use on a worksheet (not just a column). How would I enter the code to allow for multiple choices in a drop down, no duplicates, and a line break instead of a comma?

    1. Candice, make the following changes to the code in the SameCellNoDups example:
      1) Change this line, that uses the comma and space separator:

                 & ", " & newVal

      to this, which uses a line break:

                    & Chr(10) & newVal

      2) Change this line, which limits the code to column 3:

            If Target.Column = 3 Then

      to this, which allows the code in any column:

            If Target.Column > 0 Then
  18. Following Candice's comment, is there a way that I can get it to skip two lines ie. have a space between selections that fall one under the other in the same cell?
    Secondly, is there a way to make amendments to the text that is added in the editable cell. Ie. I select something from the drop down in bold but then the text I enter is unbolded?

  19. Just an update I figured out the spacing but would still like to know how to change the font and perhaps the height of the line break as well. Thank you

  20. When I delete something that was selected in error and the click off of the cell it keeps the old selections and then doubles the whole thing with the edit done. So If I removed a drop down list that said Mark, Jason, David and David was selected in error and I deleted it I would get:
    Shows as
    How can I resolve this. If I try to take duplicates out it simply doesn't work. I was able to get it to work but then I don't have the other options. Please help with this.

    1. In the sample file, on the SameCellAddRemove worksheet, there is an example that lets you remove items if they been previously selected. There is also a worksheet that shows how you can add an Edit option, and turn that feature on or off.

  21. Hi Debra,
    I am 100% new to this whole coding aspect of life. I have 2 questions. The first is that i'm having difficulty with the removal of an entry in the code. If I've selected multiple entries in my same cell and i don't need one of them or it was accidentally selected, i can't get the removal code to work. I'm positive i'm entering it wrong...
    The 2nd question is am i able to set this to work on multiple columns in a single work sheet? So where we have entered "Target.Column = #" is there a way to apply this to say columns 8 and 2?

  22. The VBA code only works in one cell. What do you do if the column contains several drop down lists and you want to be able to select multiple values from each list?

  23. Thank you for sharing this very useful tool. I have found success when I am just creating a data validation in a new spreadsheet. However, I am needing to use it in an existing spreadsheet, which has multiple worksheets, vlookups, and existing data validation. The main worksheet is in a table format. I thought that may be an issue, so I removed the table format, but it didn't seem to make any difference. The spreadsheet is not locked either. Are there any issues when using an existing spreadsheet that already uses vlookups or multiple data validation lists? I have used the both the SameCell code and the SameCellAddRemove options. Both only allow the one entry. I have also made sure I changed the code to include the correct column. Additionally, I have tried adding code in for many columns and removed the code for columns to allow for any data validation columns. So far, no luck. Any assistance would be appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.