Excel’s Data Validation list allows one selection at a time by default; there is no built-in “select multiple” setting. In Excel for Microsoft 365 on Windows, you can make a list cell add or remove choices with a short VBA macro, or use a no-macro layout that records each choice separately.
What a Data Validation List Does
A validation list gives a cell a dropdown of permitted entries. It is useful for standardizing data such as departments, status labels, product names, or task categories. To create one in Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, open the Settings tab, set Allow to List, and enter the source range or list of entries. Leave In-cell dropdown selected if you want the arrow to appear.
When someone chooses an item, Excel puts that item in the cell, replacing its previous contents. The dropdown is a way to enter one allowed value, not a checklist. Reopening the dropdown and choosing another option does not preserve the first selection. This behavior is the same in the Windows desktop app, Excel for Mac, and Excel for the web.
How to Enable Multiple Selections with VBA
A worksheet-change macro can capture the new choice, use Excel’s Undo command to retrieve the old cell value, and then combine the two. The following version toggles a choice: selecting an item that is already present removes it; selecting a new item adds it. It assumes the validation dropdown is in C2:C100 and separates choices with a vertical bar.
- Create the ordinary Data Validation list in C2:C100 using Data > Data Validation > Settings > Allow: List.
- Right-click the worksheet’s tab and choose View Code. This opens that sheet’s code module; the macro must go there, not in a standard module.
- Paste the code below into the sheet module. Change C2:C100 if your dropdown cells are elsewhere.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm), close the editor, and test by choosing two different items in one cell.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim watched As Range
Dim newValue As String
Dim oldValue As String
Dim result As String
Dim items As Variant
Dim item As Variant
Dim found As Boolean
If Target.CountLarge <> 1 Then Exit Sub
Set watched = Me.Range("C2:C100")
If Intersect(Target, watched) Is Nothing Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
On Error GoTo CleanUp
If Target.Validation.Type <> xlValidateList Then Exit Sub
newValue = CStr(Target.Value)
Application.EnableEvents = False
Application.Undo
oldValue = CStr(Target.Value)
If Len(oldValue) > 0 Then
items = Split(oldValue, " | ")
For Each item In items
If StrComp(CStr(item), newValue, vbTextCompare) = 0 Then
found = True
ElseIf Len(result) = 0 Then
result = CStr(item)
Else
result = result & " | " & CStr(item)
End If
Next item
End If
If Not found Then
If Len(result) = 0 Then
result = newValue
Else
result = result & " | " & newValue
End If
End If
Target.Value = result
CleanUp:
Application.EnableEvents = True
End Sub
For example, if C2 contains North and you choose West, the cell becomes North | West. Choosing North again leaves West. Choosing the cell’s Delete key clears it; choosing a blank entry in the list is not needed to remove a selection. The comparison ignores letter case, so it treats “North” and “north” as the same choice.
What the Macro Changes—and Its Limits
The macro only affects a single changed cell inside its watched range, and it checks that the cell has list validation. That prevents the same behavior from being applied to unrelated cells on the sheet. If you need multiple-selection behavior in more than one area, adjust the watched range or add another range check, and test the result before using it in a shared workbook.
This is not a native Data Validation feature. VBA runs in desktop Excel, but it does not run in Excel for the web. The web app can open and edit many macro-enabled workbooks and display their dropdowns, but it cannot execute the worksheet macro; selections made there will replace the cell value normally. Excel for Mac supports VBA, although the editor shortcut can depend on the keyboard. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, use Developer > Visual Basic if the keyboard shortcut is unavailable.
The example uses | as a separator, so list entries should not themselves contain that sequence. It also uses Application.Undo to read the earlier value. Undo is therefore part of the macro’s mechanism and may affect the normal undo history. Do not treat the resulting cell as if it were still a single validated choice: after the macro combines items, the whole combined text may not match any one item in the validation source.
Safety, Sharing, and Maintenance
Save a separate copy before installing or changing VBA. Macros cannot be undone with Ctrl+Z, and a coding mistake can alter workbook data. Keep a backup while testing, especially if the workbook contains important records. If the sheet is protected, the macro may be unable to write its combined result unless the protection setup permits it.
Excel may block macros based on the file’s origin or your organization’s security policy. Only run code in a workbook you trust; do not lower macro security globally just to make a dropdown work. If coworkers need the feature, tell them that they must use desktop Excel with macros permitted. A workbook that depends on VBA is a poor fit for a process that must work identically in a browser, on a phone, or in a locked-down environment.
The sample is intended for normal one-cell-at-a-time choices. It deliberately exits if a multi-cell range changes, such as when someone pastes into several cells. That avoids trying to combine a paste operation with the single-cell Undo logic. It is not a complete audit trail or a substitute for a database: users can still paste values, edit the cell, or remove the macro.
When a Different Data Layout Is Better
Putting several selections into one cell can be convenient for a compact report, but it makes later analysis harder. A cell containing North | West is text, not two separate records. Sorting, filtering, counting, and matching each choice may require extra cleanup, and a typo or a different separator can interfere with those tasks.
If the choices are data you will summarize, filter, or use in formulas, consider recording one choice per row instead. For example, a table with columns for Request ID and Category can contain several rows with the same request ID, one for each selected category. Another option is to use separate columns such as Category 1, Category 2, and Category 3 when the maximum number of choices is small and fixed. These designs keep each value distinct and are generally easier to validate and analyze.
If users truly need to mark many options, a form with checkboxes can make the task clearer than repeatedly reopening a dropdown. The right choice depends on what happens after entry: use a combined cell for a compact display, but use separate fields or rows when the values need to behave as separate data.
No-Macro Workarounds
If VBA is unavailable, there is no switch in Data Validation that turns its dropdown into a multi-select control. You can let users type more than one choice into a cell, but that is manual entry rather than multiple selection from the dropdown. It can also undermine consistent spelling and formatting. Turning off the validation error alert may allow non-list text to be entered, but it does not add selection or toggle behavior.
- Use one column per choice: Provide a dropdown in each column when only a few selections are expected.
- Use one row per choice: Store each selected item as its own record when the number of choices can vary or reporting matters.
- Use a form or separate checklist: This can provide a more natural interface for selecting many items, while storing the selections in a structured table.
These workarounds are often safer for shared files because they do not rely on macro permissions or a particular desktop app. They also make it easier for another person to understand and maintain the workbook later.
Frequently Asked Questions
Can Excel Data Validation select multiple items by default?
No. A standard list dropdown enters one item and replaces the cell’s previous value. Multiple selections require a VBA workaround or a different data-entry design.
Does the VBA multi-select dropdown work in Excel for the web?
No. Excel for the web does not run VBA macros. The workbook’s ordinary dropdown can still be used there, but each new choice replaces the previous cell value.
Can I remove one choice without clearing the whole cell?
With the toggle macro above, select the same item again and it is removed from the combined text. Without that macro, you must edit the cell manually or use a layout with one choice per field or row.
Will multiple selections still be validated against the list?
Not as a combined value. The macro writes a string containing multiple choices, which generally is not an individual item in the validation source. The original dropdown remains available, but the combined cell should be treated as macro-managed text.
Can I use a comma as the separator?
Yes, but the macro’s separator text and the matching logic must be changed consistently. A separator that does not appear inside any list item makes it easier to distinguish one choice from another.
Why does choosing another item erase the first one?
That is the normal behavior of a Data Validation list: each choice is entered as the cell’s new value. Excel does not keep a history of previous dropdown selections unless a macro or another input method adds that behavior.
Related
- Excel Drop-Down Lists: How to Create and Manage Them
- How to Insert a Checkbox in Excel: Steps and Tips
- Change Text to Numbers in Excel: Easy, Reliable Methods
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Cannot Open the File: Causes, Checks, and FixesYes—Excel may be able to open a file that it says is corrupted, but the message does not…
- How to Double Space in Word for FreeTo double-space text in Word for free, open your document in Word for the web, select the text,…
- Variance and Box Plots in Excel: What Each ShowsExcel’s Box and Whisker chart does not show variance by default. Create the chart to compare medians, quartiles,…
- Outlook Signature in Windows: How to Create OneTo create a signature in Outlook for Windows, open the signature settings for your version of Outlook, create…
- How to Lock Cells in Excel So You Can ScrollTo keep headings or identifying columns visible while you scroll through a worksheet, use Freeze Panes: select the…
- Excel Shortcut to Insert a Copied RowIn Excel for Microsoft 365 on Windows, copy the entire row, select the row where the copy should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.