Excel Data Validation List: Can You Select Multiple?

How-to basics · Updated November 19, 2026

Not with Excel’s built-in Data Validation list: a standard dropdown lets you choose only one item per cell at a time. In Excel for Microsoft 365 on Windows, you can make a list cell keep multiple choices by adding a VBA worksheet macro; without macros, use separate cells or another input design.

What Excel’s built-in list does

Data Validation restricts or guides what someone enters in a cell. For a dropdown, select the cell or range, then choose Data > Data Validation. In the Settings tab, set Allow to List and identify the allowed entries in Source. The source can be a range, a named range, or a list of items, depending on how the workbook is set up.

When a user picks an option, Excel writes that option into the cell as its value. Choosing a different option replaces the existing value; it does not append the new choice. There is no built-in checkbox or “allow multiple selections” setting in the Data Validation dialog. This is true even if the dropdown is based on a carefully maintained source range.

That behavior is useful when a cell should contain one unambiguous answer, such as a department, status, or shipping method. It becomes a problem when the cell is intended to represent several tags, skills, or categories. The key design question is whether multiple choices truly belong in one cell, or whether the worksheet should store one choice per cell or row instead.

Decide how the selections should be stored

A multi-select dropdown usually joins choices into a text string, for example Red | Blue | Green. Although it may look like several values, Excel sees the whole string as one cell value. That distinction matters for sorting, filtering, formulas, charts, and exports. A cell containing “Red | Blue” is not the same as two separate records that each contain one color.

Before adding a workaround, consider who will use the workbook and what will happen to the data later. A combined list can be convenient for a small tracker that people read and edit manually. It is often a poor format for reporting, counting selections, or sending data to another system. If “one person can have several skills” is a core data relationship, recording each person-skill combination on its own row is usually easier to analyze.

  • Use one dropdown choice per cell when each record has exactly one answer.
  • Use separate columns when the number of choices is small and fixed, such as First choice, Second choice, and Third choice.
  • Use one row per selected item when you need reliable filtering, counting, or later analysis.
  • Consider a combined text value only when compact, human-readable entry matters more than structured analysis.

Add multi-select behavior with VBA

A worksheet event macro can remember the previous cell value and combine it with the newly selected dropdown item. The example below applies to cells B2:B100 on the worksheet where the code is installed. It separates choices with a vertical bar and spaces, avoids adding the same choice twice (without regard to letter case), and lets a user clear the cell.

  1. Create the ordinary single-choice dropdown first. Select B2:B100 and configure Data > Data Validation > Settings > Allow: List.
  2. Open the Visual Basic Editor. On Windows, Developer > Visual Basic opens it; if Developer is not shown, enable that tab in Excel Options. In the editor, find the worksheet containing the dropdowns in the Project pane and open that sheet’s code window. This is a worksheet event, so do not put it in a standard module.
  3. Paste the code into that worksheet’s code window. Change B2:B100 if your dropdown range differs. Choose a separator that does not occur inside any list item.
  4. Save the workbook 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.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim priorValue As String
    Dim chosenValue As String
    Dim separator As String

    On Error GoTo CleanUp

    If Target.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range("B2:B100")) Is Nothing Then Exit Sub
    If Target.Validation.Type <> xlValidateList Then Exit Sub

    chosenValue = CStr(Target.Value)
    separator = " | "
    Application.EnableEvents = False
    Application.Undo
    priorValue = CStr(Target.Value)

    If Len(chosenValue) = 0 Then
        Target.ClearContents
    ElseIf Len(priorValue) = 0 Then
        Target.Value = chosenValue
    ElseIf InStr(1, separator & priorValue & separator, _
        separator & chosenValue & separator, vbTextCompare) > 0 Then
        Target.Value = priorValue
    Else
        Target.Value = priorValue & separator & chosenValue
    End If

CleanUp:
    Application.EnableEvents = True
End Sub

After the code is installed, choosing “Red” and then “Blue” in the same cell produces Red | Blue. Choosing “Red” again leaves the existing text unchanged. The macro uses Application.Undo to retrieve the cell’s previous value, so it is designed for a direct dropdown selection in one cell—not for processing a multi-cell paste or a series of arbitrary edits.

Important limits of the VBA approach

This is a workaround, not a native multi-select feature. The cell still contains a single text string. A validation rule’s “Stop” error alert normally rejects values that are not in the source list, but the macro writes the combined string after the selection, so that combined value is not itself one of the listed options. Users should make changes through the dropdown and understand the intended format.

Macros must be enabled for the behavior to work. Excel for the web cannot run VBA macros, so the dropdown behaves as a normal single-select list when the workbook is opened there. Excel for Mac supports VBA in its desktop application, but editor access and keyboard shortcuts can vary by Mac setup; the web limitation still applies. If colleagues use different platforms or have macro policies that block code, do not make this workaround essential to completing the workbook.

There is also an undo trade-off. The macro calls Excel’s undo command internally and then writes a new value, so the selection may not be reversible with the usual Ctrl+Z history in the way users expect. Macros cannot be undone with Ctrl+Z. Save a copy before adding or running VBA, and test the behavior with representative entries before rolling it out. The example also does not remove an individual item from the middle of a combined value; clearing the cell and selecting the desired items again is the simple removal method.

Alternatives that do not require a macro

If users need to choose several items but the workbook must work in Excel for the web, a macro-free layout is generally more dependable. The best option depends on how many choices are possible and how the results will be used.

  • One choice per row: Give each selected item its own row, with an ID or person name repeated beside it. For example, a skills table might have one row for “Jordan — Excel” and another for “Jordan — PowerPoint.” This is easy to filter and summarize.
  • Several fixed dropdown columns: Add columns such as Choice 1, Choice 2, and Choice 3, each with the same validation list. This is straightforward for a small maximum number of selections, though it can be awkward if the number varies widely.
  • Checkboxes: A set of checkboxes can make several choices visible at once. Each checkbox needs a linked cell to record its state, so the sheet can become wide or crowded when the list is long. Controls and their setup may also behave differently across Excel platforms.
  • Manual delimiter-separated entry: Let users type values such as Red; Blue and document the exact spelling and separator. This avoids VBA, but spelling variations and inconsistent separators make the data harder to clean and analyze.
  • A form or database-style input tool: If the process is important and involves many users, a purpose-built form or data system may capture multiple selections as separate records more reliably than a single spreadsheet cell.

For reporting, separate records are usually the strongest choice. For a compact, informal worksheet used only in desktop Excel with trusted macros, the VBA method can be a practical convenience. A useful test is to ask whether you will later need to count how many records include “Blue.” If so, storing “Blue” as an individual value will usually make that task simpler.

Common problems and how to avoid them

  • The dropdown still replaces the old choice: Confirm the code is in the correct worksheet’s code window, not a standard module, and that the selected cell is inside the range named in Me.Range. Check that macros are enabled.
  • The macro affects the wrong cells: Replace B2:B100 with the exact intended range. If the range includes cells without list validation, the validation-type check is intended to leave those cells alone.
  • Choices appear joined incorrectly: Check the separator assignment in the code. Do not use a separator that can appear within a valid option, or it may be impossible to distinguish a separator from part of an item.
  • A selection is repeated: The sample checks for an exact item bounded by the separator and ignores case. If your entries contain the separator, or if you need case-sensitive matching, this simple text-based approach may not fit your list.
  • Ctrl+Z does not restore the previous state: That is an expected consequence of using VBA and Application.Undo. Keep a backup and test edits on a copy.
  • It works on desktop but not in a browser: Excel for the web does not execute VBA. Use a macro-free design for workflows that must operate in the browser.

Frequently Asked Questions

Can Excel Data Validation allow multiple selections by itself?
No. Its built-in list dropdown writes one choice to the cell. Multiple selections require a workaround, such as a VBA event macro, or a different worksheet layout.

Can I select multiple items from a dropdown without VBA?
Not in one standard Data Validation cell. You can use separate dropdown cells, checkboxes, separate rows, or manually enter a consistently formatted list. These approaches avoid relying on a VBA macro.

Does the multi-select VBA dropdown work in Excel for the web?
No. Excel for the web does not run VBA. The workbook can still contain a validation list, but selecting an item in the browser will not append it using this macro.

How do I remove one selection from the cell?
The sample macro does not toggle off an individual choice. Clear the cell and choose the remaining items again, or use a different design such as one row per selection if frequent edits are expected.

Can I use commas between selections?
Yes, the separator is editable in the code. Pick a character or sequence that is not part of any valid list item, and use the same separator consistently if the values will be parsed later.

Will the combined cell work like separate values in formulas and filters?
Not automatically. Excel treats the combined text as one value, so exact-match filters and calculations may not recognize its components as individual selections. Store one item per cell or row when analysis matters.

Related

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.