Excel Data Validation List: Multiple Selections

In Microsoft Excel for Microsoft 365 on Windows, a data validation list allows only one selection per cell by default. To keep multiple choices in the same cell, you need a VBA macro; if macros are unsuitable, use a different layout such as one choice per cell or a set of checkboxes.

What an Excel data validation list does

A data validation list puts a dropdown arrow in a cell and restricts entries to a specified set of values. It is useful for consistent labels such as department names, order statuses, or product categories. When someone chooses an item, Excel puts that item into the cell, replacing whatever was there before. The built-in list setting has no option to turn on multiple selections.

This is different from selecting several cells at once, which you can do by dragging or holding Ctrl while clicking cells. It is also different from a checkbox list: a dropdown is a way to enter one value in a cell, not a collection of independent on/off controls. If you select “Urgent” and then choose “Review” from the same dropdown, the cell contains “Review,” not both values.

That default behavior is often exactly what a workbook needs. It keeps each cell easy to filter, sort, count, and use in formulas. Multiple selections can be convenient for a quick entry form, but they change how the data is stored and can make later analysis less straightforward.

Create the dropdown before adding multi-select behavior

In Excel for Microsoft 365 on Windows, select the cells that should have a dropdown, then choose Data > Data Validation > Data Validation. On the Settings tab, set Allow to List. Enter the permitted values in Source, separated by commas, or select a range containing the values. Keep In-cell dropdown selected and choose an appropriate error alert if users should not type unlisted values. Select OK.

A source range is usually easier to maintain than a long comma-separated entry. For example, put department names in a separate sheet, select those cells as the Source, and update the source list when departments change. If the source list may grow, consider using an Excel table as the source-list foundation; the exact source-reference setup depends on how the workbook is organized.

Use the same validated range consistently. The macro below is set up for cells B2:B100, so the dropdown should be applied to that range.
Test the ordinary dropdown first. Choose one value and confirm that the permitted items and error behavior are correct.
Save a copy of the workbook before adding code. VBA changes how the workbook behaves and may be blocked by organizational security settings.

Use VBA to append selections in the same cell

For a multi-select dropdown in desktop Excel, a worksheet event macro can remember the cell’s previous value and combine it with the newly selected value. The following example adds each new choice after the existing text, separates choices with “ | ”, and avoids adding the same choice twice. It treats duplicates without regard to letter case.

To add it on Windows, right-click the worksheet tab that contains the dropdown and choose View Code. Paste the code into that worksheet’s code window, not into a standard module. Change B2:B100 if your dropdown cells are elsewhere. The range should contain the validated cells where the behavior is wanted.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldValue As String
    Dim newValue As String
    Dim delimiter As String

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

    On Error GoTo RestoreEvents
    delimiter = " | "
    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
    ElseIf InStr(1, delimiter & oldValue & delimiter, _
                 delimiter & newValue & delimiter, vbTextCompare) = 0 Then
        Target.Value = oldValue & delimiter & newValue
    Else
        Target.Value = oldValue
    End If

RestoreEvents:
    Application.EnableEvents = True
End Sub

After adding the code, save the file as an Excel Macro-Enabled Workbook (.xlsm). Close and reopen it if needed, and enable macros only when you trust the workbook and its source. On a Windows keyboard, Alt+F11 opens the Visual Basic Editor; right-clicking the sheet tab is another route to its code window. Excel for Mac also supports VBA in the desktop app, although the editor access and keyboard shortcuts can differ by keyboard and version. Excel for the web can open a macro-enabled workbook, but it does not run VBA macros.

How to use the multi-select dropdown

Choose a value from the dropdown in a cell covered by the macro. Choose another value in that same cell, and the macro adds it after the first, for example Planning | Review. Choosing “Review” again leaves the text unchanged rather than adding a duplicate. The separator is part of the cell’s text, so choose a delimiter that does not appear inside any of the permitted list items.

To remove all choices from a cell, select it and press Delete. The macro clears the cell when the new value is blank. To remove just one item, edit the text manually, taking care to remove the separator as well, or use a different design that stores each choice separately. This example does not provide a toggle that removes an item when it is selected a second time; repeated selections are simply ignored.

The code uses Application.Undo to retrieve the old cell value after Excel has recorded the new selection. That approach has an important side effect: running VBA clears Excel’s normal undo history. The macro’s own changes cannot be reversed with Ctrl+Z, so save a copy before trying it and use caution when editing important data. Mac keyboard shortcuts for undo differ from Windows, but the limitation is the same.

What changes when you store several values in one cell

Combining choices into a text string can make a compact input sheet, but it is not equivalent to storing each choice as a separate record. Excel sees Planning | Review as one text value. A filter for the exact value “Review” will not necessarily treat that combined entry as the individual category “Review,” and counting or summarizing choices may require extra cleanup or text-processing steps.

There are other practical trade-offs. The combined text may become too long to read comfortably in a narrow column. If an item name contains the chosen separator, it can be difficult to tell where one item ends and the next begins. Changing the dropdown’s allowed items also does not automatically remove old, now-invalid values already stored in cells. Consider who will maintain the workbook and how the results will be used before adopting this pattern.

For reporting, a normalized layout is often more dependable: create a separate row for each selected item, linked to the relevant record by an ID. For a small form, another option is to dedicate a column to each possible choice and enter a mark or TRUE/FALSE value. These layouts use more cells, but they make individual selections easier to count, filter, and validate.

Alternatives when VBA is not a good fit

If a workbook must work in Excel for the web, be shared with people who cannot enable macros, or pass through strict company security controls, do not rely on this VBA event. Choose a layout that works without code instead. The right alternative depends on whether the priority is simple data entry, clean analysis, or a compact display.

One selection per row: Repeat the record ID on multiple rows and put one selected item in each row. This is often the clearest structure for filtering and summaries.
One column per option: Provide a separate checkbox or entry field for each known choice. This is easy to inspect when the option set is small and stable.
Separate selection cells: Provide several validated cells beside the record, one for each allowed choice. This preserves dropdowns without combining values in text.
Manual delimited text: Let users type several values in a cell using an agreed separator. This avoids VBA, but does not enforce each value as a valid dropdown choice.

Excel’s standard Data Validation List setting does not itself offer a checkbox-based multi-select dropdown. Some add-ins or custom form controls may provide other interfaces, but they bring their own compatibility and maintenance requirements. A normal validated list should not be described as multi-select merely because a cell can contain manually typed text with several words.

Common problems and fixes

The second choice replaces the first: The event code is missing, stored in the wrong worksheet’s code window, or macros are disabled. Confirm that the code belongs to the sheet with the dropdown and that the file is saved as .xlsm.
The macro does nothing: Check that the edited cell falls inside the range in the code. If the dropdown is in D2:D50, for example, replace B2:B100 with D2:D50.
Every edited cell gets combined text: The range restriction may be too broad. Limit the code to the exact cells intended for multi-select and avoid placing unrelated input in that range.
Multiple cells are pasted or cleared at once: This example exits when more than one cell changes. It is designed for one dropdown choice at a time, not bulk pasting.
Ctrl+Z does not undo the result: That is a consequence of the VBA method, not a setting to fix. Restore a saved copy or edit the cell deliberately; the macro does not preserve the ordinary undo stack.

If events stop firing after an unexpected VBA error, closing and reopening the workbook can restore normal behavior. The sample includes an exit section to turn events back on after an error, but any edited macro should still be checked carefully. Do not use code from an untrusted workbook simply to enable a dropdown feature.

Frequently Asked Questions

Can Excel data validation allow multiple selections by default?
No. A standard List validation dropdown writes one choice into the cell. Keeping previous choices requires VBA or a different workbook layout.

Can I use a multi-select dropdown in Excel for the web?
Excel for the web does not run VBA event macros. Use separate cells, rows, or columns for selections if users need the workbook to work in a browser.

Will the macro work in Excel for Mac?
Desktop Excel for Mac supports VBA, but the editor and shortcut details can differ. The macro is attached to a worksheet, so test it in the Mac version and file format used by the workbook’s audience.

Can selecting an item a second time remove it?
Not with the example shown. It ignores duplicates. Removing individual choices requires editing the cell or writing a different macro with explicit toggle behavior.

Does a cell with several choices still behave like a list value?
It remains text in a cell, but the combined string is not the same as any one list item. Exact-match filters and summaries may not treat its parts as separate values.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.