Excel data validation dropdowns allow only one selection per cell by default; there is no built-in multi-select switch in Microsoft 365 for Windows. To keep adding dropdown choices in the same cell, use a VBA worksheet event macro; if macros are unsuitable, use a checklist or separate columns instead.
What Excel data validation supports
Data validation can restrict what a user enters in a cell, including showing a dropdown of permitted values. To create one, select the cells, then choose Data > Data Validation > Data Validation. On the Settings tab, set Allow to List, provide a source range or list, and leave In-cell dropdown selected if you want the arrow to appear. Each choice replaces the cell’s current value when selected.
That behavior is useful for a field that should contain exactly one answer, such as a department, status, or product type. It does not provide a built-in way to select several options at once. A user can type several values into a cell, but that is not the same as the dropdown supporting multiple selections: validation may reject the entry, and there is no automatic way to combine successive choices.
Online instructions sometimes describe a “multi-select” option in Data Validation. In desktop Excel, that generally means adding VBA or using a different data-entry design, not turning on a native setting. Excel for the web can display and use ordinary validation dropdowns, but it does not run VBA macros.
How the VBA multi-select method works
A worksheet change macro can remember the cell’s previous value, undo the latest dropdown selection, and write both the previous and newly selected values back into the cell. The result might be Red, Blue, Green in one cell. On the next selection, the macro appends the new choice, provided it is not already present.
This approach is convenient when people specifically need a compact, comma-separated summary in one cell. It is still a workaround: Excel continues to store the result as one text string, not as three independent selections. For analysis, filtering, or reporting, separate values in separate cells or rows are often easier to work with.
Set up a multi-select dropdown with VBA
- Create the ordinary list validation first. For example, select B2:B100, choose Data > Data Validation > Data Validation, set Allow to List, and specify the permitted values.
- Save a backup copy of the workbook. Then save the working file as an Excel Macro-Enabled Workbook (
.xlsm); a standard.xlsxfile cannot retain VBA code. - Open the Visual Basic Editor. In Windows Excel, use Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. In Excel for Mac, enable the Developer tab through Excel > Preferences > Ribbon & Toolbar, then open Visual Basic from that tab.
- In the editor’s project pane, find the worksheet that contains the dropdowns, such as Sheet1, and double-click it. Paste the code below into that worksheet’s code window, not into a standard module.
- Change the example range B2:B100 in the code if your dropdown cells are elsewhere. Save, close the editor, and test by selecting two different entries in a validated cell. When reopening the workbook, allow macros only if you trust the file and its source.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim newValue As String
Dim oldValue As String
Dim delimiter As String
Dim item As Variant
Dim eventsWereEnabled As Boolean
' This example applies only to the dropdown cells in B2:B100.
If Target.CountLarge <> 1 Then Exit Sub
If Intersect(Target, Me.Range("B2:B100")) Is Nothing Then Exit Sub
eventsWereEnabled = Application.EnableEvents
On Error GoTo CleanUp
' Continue only for cells with list validation.
If Target.Validation.Type <> xlValidateList Then Exit Sub
newValue = CStr(Target.Value2)
If Len(newValue) = 0 Then Exit Sub
delimiter = ", "
Application.EnableEvents = False
Application.Undo
oldValue = CStr(Target.Value2)
If Len(oldValue) = 0 Then
Target.Value2 = newValue
Else
For Each item In Split(oldValue, delimiter)
If StrComp(Trim$(CStr(item)), Trim$(newValue), vbTextCompare) = 0 Then
Target.Value2 = oldValue
GoTo CleanUp
End If
Next item
Target.Value2 = oldValue & delimiter & newValue
End If
CleanUp:
Application.EnableEvents = eventsWereEnabled
End Sub
The example ignores a choice that is already in the cell, comparing entries without regard to uppercase or lowercase. It does not remove an existing choice when you select it again. To clear the cell, select it and press Delete; the empty value is left blank. The code assumes list items do not contain the delimiter comma followed by a space, because that text is used to separate saved choices.
Important limits and trade-offs
The macro belongs to one worksheet’s code module, and the example deliberately limits its action to B2:B100. If you paste a block of cells, the code exits without processing it. If you copy the sheet, check that the code and intended range are present on the copied worksheet too. A macro that changes the range or applies across multiple sheets needs to be adjusted accordingly.
VBA also changes how the workbook must be shared. Recipients need desktop Excel with macros permitted to use the automatic appending behavior. Excel for the web does not execute the macro, and organizational security settings may block macros. If a recipient opens the file with macros disabled, the cells still have ordinary validation, but each new dropdown choice replaces the previous one.
There is another practical drawback: the macro uses Application.Undo to recover the cell’s earlier value. Running VBA clears Excel’s normal undo history, and a macro action cannot be reversed with Ctrl+Z. Keep a backup and test on sample cells before using the method in a workbook that contains important data.
Choose a delimiter and plan for clean data
Comma-space is readable, but it can make later splitting ambiguous if a permitted choice itself contains a comma. Use a delimiter that cannot appear in any list item, or choose a different layout. Also decide whether repeated selections should be allowed. The supplied code suppresses exact repeated choices, ignoring case and surrounding spaces, but it does not standardize capitalization or reorder the selections.
For example, if the allowed choices are Planning, Design, and Review, one cell might end up containing Planning, Review. A later formula, pivot table, or filter sees that whole string as one cell value. It will not automatically treat Planning and Review as two separate records. If you need to count how many records include each choice, a normalized table with one choice per row is usually a better source for analysis.
Alternatives when a macro is not appropriate
- Use one column per option: Add a Yes/No dropdown, checkbox, or other clear indicator under each option. This makes it easy to filter and count selections, though the table becomes wider.
- Use one row per selection: Keep a record ID in one column and put each chosen item on its own row. This is usually easier to summarize and avoids parsing a combined text field.
- Use a checklist: Checkboxes can suit a small, fixed set of choices on a form. They take more room than a single dropdown and need a clear plan for storing or summarizing each checked state.
- Allow typed entries: A user can enter a delimiter-separated list manually if the process is simple, but validation and instructions must account for spelling, spacing, duplicates, and separators.
Choose based on what happens after data entry, not only on how compact the form looks. If the main goal is to store a readable note, a macro-generated list may be adequate. If people will filter, count, or report on each individual selection, store those selections separately.
Common problems and fixes
- The next selection replaces the old one: Check that the code is in the correct worksheet module, the file is saved as
.xlsm, and macros are enabled. A copy of the sheet may not have the code you expect. - The macro has no effect in a browser: Excel for the web does not run VBA. Open the workbook in desktop Excel or use an alternative that does not depend on the macro.
- Excel reports an error or events stop firing: The code restores the prior events setting at its cleanup label, but test after any code edits. If another macro has left events disabled, a worksheet change event will not run until events are enabled again.
- Duplicate choices appear: The example checks for a repeated whole item, but list entries and delimiters should be consistent. Avoid adding commas or extra spaces to the actual choice names.
- Ctrl+Z does not undo the selection: That is expected after a VBA procedure runs. Test in a copy, and use the workbook’s version history or a backup to recover earlier data if necessary.
Frequently Asked Questions
Can Excel data validation allow multiple selections by itself?
No. A standard validation list accepts one choice per cell at a time. A VBA event macro can append choices in desktop Excel, or you can change the form design to record each selection separately.
Can I select multiple items from an Excel dropdown using Ctrl or Shift?
No. Ctrl- or Shift-click does not turn a standard data validation dropdown into a multi-select control. The dropdown replaces the cell’s current value unless a macro or another design handles selections differently.
Does the VBA method work in Excel for the web?
No. The web version can use ordinary data validation, but it does not run VBA. Use desktop Excel with macros allowed, or choose a non-macro approach for a workbook that must work in a browser.
Can I remove just one selected item?
Not with the supplied code. It appends choices and prevents duplicates, but does not provide a removal action. You can edit the cell’s text manually, or ask for a different macro design that includes a removal rule.
Will multi-select values work normally in filters and formulas?
The combined text can be searched or compared as text, but Excel treats the cell as one value. To count or summarize individual choices reliably, separate the selections into individual fields or rows.
Related
- Excel Dropdown: Remove Duplicate Options Easily
- Excel Dropdown List: Create, Edit, and Troubleshoot
- Does Excel Online Have Goal Seek? What to Use Instead
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Double Space in Word for a Document or SelectionTo double-space text in Microsoft Word, select the text—or press Ctrl+A to select the document—then choose Home >…
- Can You Make Columns in Word? A Quick GuideYes. In Microsoft Word for Microsoft 365 on Windows, select the text you want to format, then choose…
- Excel Shortcut to Expand a Column: AutoFit and MoreIn Excel for Microsoft 365 on Windows, select a cell in the column and press Alt, H, O,…
- Excel Column Won’t Unhide? Causes, Checks, and FixesIf an Excel column will not unhide, select the columns on both sides of it—or select the whole…
- Excel Time Difference in Seconds: How to CalculateWith a start time in A2 and an end time in B2, enter =(B2-A2)*86400 to calculate the difference…
- Google Sheets QUERY ORDER BY: Sort Query ResultsIn Google Sheets, use the order by clause inside a QUERY formula to sort the rows it returns.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.