Excel Macro for Multiple-Selection Drop-Down Lists

To let a user choose several items from one Excel drop-down, add a VBA Worksheet_Change macro to the worksheet; it can append each new choice to the cell, separated by a delimiter. Excel’s standard Data Validation list allows only one choice per cell, so the macro below supplies the multiple-selection behavior in the desktop Excel app.

How the Multiple-Selection Macro Works

The worksheet event runs after a cell changes. It reads the newly selected value, uses Application.Undo to retrieve the cell’s previous value, and then writes the old and new choices together. This example uses a semicolon followed by a space as the separator. It avoids adding the same item twice, ignoring differences in letter case.

Before installing it, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and an error or unwanted change may require restoring the copy.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NewValue As String
    Dim OldValue As String
    Dim Delimiter As String
    Dim Items As Variant
    Dim Item As Variant
    Dim Found As Boolean

    Delimiter = "; "

    ' Handle one validated cell in the intended input range only.
    If Target.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range("B2:B100")) Is Nothing Then Exit Sub

    On Error GoTo CleanUp
    If Target.Validation.Type <> xlValidateList Then Exit Sub

    NewValue = CStr(Target.Value2)
    Application.EnableEvents = False
    Application.Undo
    OldValue = CStr(Target.Value2)

    If Len(NewValue) = 0 Then
        Target.ClearContents
    ElseIf Len(OldValue) = 0 Then
        Target.Value = NewValue
    Else
        Items = Split(OldValue, Delimiter)
        For Each Item In Items
            If StrComp(Trim$(CStr(Item)), NewValue, vbTextCompare) = 0 Then
                Found = True
                Exit For
            End If
        Next Item

        If Found Then
            Target.Value = OldValue
        Else
            Target.Value = OldValue & Delimiter & NewValue
        End If
    End If

CleanUp:
    Application.EnableEvents = True
End Sub

The macro watches cells B2:B100 on the worksheet where it is installed. Change that range in the code if your validated input cells are elsewhere. The duplicate check treats “Blue” and “blue” as the same choice. If a user selects an item already in the cell, the existing list stays as it is; the code does not remove that item.

Set Up the Drop-Down and Install the Code

First create the ordinary Data Validation list. For example, place choices such as Red, Blue, and Green in a source range. Select the intended input cells, then on Windows use Data > Data Tools > Data Validation. In the Settings tab, choose List under Allow and set the Source to the cells containing the choices. Confirm that the in-cell drop-down option is enabled.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As.
Right-click the worksheet tab that contains the input cells and choose View Code. The event code belongs in that worksheet’s code window, not in a standard module.
Paste the complete macro into the code window. Replace B2:B100 with the range that should allow multiple choices.
Return to the workbook and save. If Excel asks about macros when reopening the file, enable them only if you trust the workbook and its source.
Choose an item from a validated cell’s drop-down, then choose another. The cell should show both choices, such as Red; Blue.

On Windows, Alt+F11 opens the Visual Basic Editor; on some keyboards you may need Fn+Alt+F11. On Mac, use Option+F11, sometimes with Fn as well, or open the Visual Basic Editor from Excel’s Developer tab. The VBA logic is intended for desktop Excel on Windows; Mac desktop Excel supports VBA, though keyboard shortcuts and security prompts can differ. Excel for the web does not run worksheet VBA events, so this behavior requires opening the workbook in desktop Excel.

What the Cell Will Contain

Suppose B2 has a validated list with Red, Blue, and Green. Selecting Red makes the cell contain Red. Selecting Blue afterward changes it to Red; Blue. Choosing Green next produces Red; Blue; Green. This is one text value in a cell, not three separate values as far as Excel is concerned.

The macro also allows a user to clear a cell: deleting its contents leaves it blank. Selecting a choice again after clearing starts a new list. Because the macro is limited to a single cell at a time, pasting into multiple cells does not run the combination logic; the code exits when the changed range contains more than one cell.

Change the delimiter by editing the line that sets Delimiter, for example to ", " for comma-separated text. Avoid choosing a separator that can also appear inside a list item. If an item itself contains the delimiter, the duplicate-checking split can interpret it as more than one item.

Important Limits and Data-Handling Trade-Offs

Multi-selection is convenient for a short form, but it changes the meaning of the cell. Excel sees “Red; Blue” as one text string. Sorting, filtering, counting, or looking up individual choices is therefore less straightforward than when each choice occupies its own cell or row. A normal filter for “Blue,” for example, may not match a cell containing “Red; Blue” as an exact value.

Use this approach when people need a compact, readable summary and downstream analysis is limited. If selections will feed reports, PivotTables, formulas, or exports, a more structured layout is usually better. One useful design is a separate row for each person-choice pair, with columns such as Person and Choice. Another is to provide several independently validated cells, one for each allowed selection.

One-cell list: compact for data entry, but requires text parsing for analysis.
Separate selection columns: easy to inspect and filter, but uses more columns and may limit the number of choices.
One row per choice: works well for reporting and filtering, but takes more rows and may need a form or other entry method.

Common Problems and Troubleshooting

If a second choice replaces the first, check that the code is in the correct worksheet’s code window and that macros are enabled. Also confirm that the edited cell falls inside the range named in Me.Range("B2:B100"). The macro does not apply workbook-wide: adding it to one sheet does not make it work on another sheet.

If nothing happens, confirm that the input cells really use a Data Validation list. The code checks for the list validation type and exits for cells without it. If you change the validation range or replace the cells, verify that the new cells still have the expected validation rule.

If the macro causes other worksheet events to stop working, Excel’s events may have been left disabled after an interruption. The cleanup section restores Application.EnableEvents when the code reaches it, but an abrupt VBA reset can bypass cleanup. In the Visual Basic Editor’s Immediate window, enter Application.EnableEvents = True and press Enter to restore events. Use this only when you understand the workbook’s code and have saved a copy.

The code relies on Excel’s Undo command to recover the previous cell value. That means it is designed for a user changing one validated cell directly, not for a batch paste or a different automation process. VBA also affects Excel’s normal undo history, so do not expect Ctrl+Z to reverse the macro’s combined result.

Security, Sharing, and Compatibility

A macro-enabled workbook can contain executable code, so only enable macros in files from sources you trust. Do not weaken Excel’s macro security settings globally just to make one workbook work. An organization may require a trusted location or a digitally signed macro; follow its policy. A worksheet-protection setup can also affect whether users are allowed to change the validated cells.

When sharing the workbook, tell recipients that the multiple-selection feature needs desktop Excel with macros enabled. In Excel for the web, users can view workbook content, but this VBA event does not run when they change a cell. If a recipient opens the file in an app that does not support VBA, the stored text may still be visible, but the automatic append behavior should not be expected.

Alternatives Without This Macro

There is no built-in setting in ordinary Excel Data Validation that turns a single-choice list into a multi-select list. A formula in the same cell is not a direct substitute: formulas calculate from inputs, but they do not capture the previous value each time a user chooses a new list item. If macros are prohibited, use multiple validated cells, a separate table for each selection, or a form-based workflow that records one choice per row.

For a small fixed number of choices, separate columns such as Choice 1, Choice 2, and Choice 3 are simple to set up and analyze. For an open-ended number, a table with one selection per row is more flexible. These layouts are less compact than a combined text cell, but they avoid parsing strings later and work better with ordinary Excel filters and summaries.

Frequently Asked Questions

Can Excel’s Data Validation drop-down select multiple items by itself?
No. A standard Data Validation list selects one item per cell. VBA or a different data-entry design is needed to collect multiple choices.

Does this macro work in Excel for the web?
No. Excel for the web does not execute this VBA worksheet event. Open the macro-enabled workbook in desktop Excel to use the feature.

Can I use commas instead of semicolons?
Yes. Change the delimiter assignment in the macro to a comma and a space. Avoid that separator if list items themselves can contain it.

How do I remove one choice from the combined cell?
This version prevents a duplicate selection but does not toggle a choice off. The straightforward way to remove a choice is to edit the cell’s text, or use a modified macro designed to remove selected items. Clearing the whole cell and selecting again also starts over.

Why can’t I undo the result with Ctrl+Z?
The macro uses Application.Undo to recover the cell’s previous value, and running VBA disrupts Excel’s ordinary undo history. Save a copy before using the macro and correct the cell manually if needed.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.