Create an Excel Drop-Down List With Multiple Selections and No Duplicates
Question details
Create an Excel drop-down that allows multiple selections in one cell without repeating an item.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to select several distinct values from a data-validation list and keep them in the same cell.
- Observed behavior
- Standard Excel data-validation lists replace the previous selection instead of accumulating multiple values.
Open the workbook in desktop Excel and identify the cells that need the drop-down. Prepare a source list whose items do not contain the separator comma followed by a space, and use a workbook you trust because the solution requires macros.
Use a worksheet VBA macro to append unique selections
Use this method when each cell should retain multiple selections from an Excel data-validation list.
The macro below applies to cells A2:A100 on one worksheet. It separates selections with a comma and a space and ignores repeated selections without regard to letter case. It keeps items in the source list rather than removing them.
Excel's Undo history may be affected by the macro. Excel for the web cannot run VBA macros, so make and test selections in desktop Excel.
Select A2:A100 on the target worksheet, then choose Data > Data Validation. Set Allow to List, enter or select your source range, and click OK.
Right-click the target worksheet tab and choose View Code. Paste the following into that worksheet's code window. If it already contains a Worksheet_Change procedure, integrate the logic into that procedure instead of adding a second one. Private Sub Worksheet_Change(ByVal Target As Range) Dim newValue As String Dim oldValue As String Dim item As Variant If Target.CountLarge > 1 Then Exit Sub If Intersect(Target, Me.Range("A2:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp newValue = CStr(Target.Value2) If Len(newValue) = 0 Then Exit Sub Application.EnableEvents = False Application.Undo oldValue = CStr(Target.Value2) If Len(oldValue) = 0 Then Target.Value = newValue Else For Each item In Split(oldValue, ", ") If StrComp(Trim(CStr(item)), Trim(newValue), vbTextCompare) = 0 Then Target.Value = oldValue GoTo CleanUp End If Next item Target.Value = oldValue & ", " & newValue End If CleanUp: Application.EnableEvents = True End Sub
Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). When you reopen a trusted workbook, use the security prompt to enable its macros.
In A2, select one list item and then another. Select the first item again to confirm it is not repeated. To clear all selections, select the cell and press Delete.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday workbook work between suites. The Excel-specific VBA solution above is not guaranteed to run in WPS Office; check macro support before relying on it.

Frequently Asked Questions
Can a standard Excel drop-down select multiple values in one cell?
No. A standard list created through Data > Data Validation replaces the existing selection. A worksheet VBA macro or a different workbook design is needed to retain multiple selections.
How does the macro prevent duplicate selections?
It splits the existing cell contents at each comma and space, then compares each item with the new selection without regard to letter case. If it finds a match, it restores the existing contents.
Can I remove one selected item from the cell?
The example macro does not provide individual removal. Select the cell and press Delete to clear all selections, then choose the items you want again.
Why does the drop-down stop working after I reopen the workbook?
Check that you saved it through File > Save As as an .xlsm file and enabled macros from the security prompt in desktop Excel. The VBA code cannot run in Excel for the web.
Does the macro remove selected items from the source list?
No. The source list remains unchanged; the macro prevents an item from being repeated within the same target cell. Removing items from the source list requires a different design.




