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.
- Create the ordinary single-choice dropdown first. Select B2:B100 and configure Data > Data Validation > Settings > Allow: List.
- 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.
- Paste the code into that worksheet’s code window. Change
B2:B100if your dropdown range differs. Choose a separator that does not occur inside any list item. - 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; Blueand 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:B100with 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
- How to Combine Excel Files: Methods and Common Issues
- Combine Multiple XML Files Into One Excel Workbook
- Combine Excel Files: Practical Methods and Steps
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 Autofill in Excel From the Cell Directly AboveTo fill cells from the one above in Excel, select the source cell and the cells below it,…
- Google Sheets Data Validation: How to Add RulesTo add data validation in Google Sheets, select the cells, choose Data > Data validation, click Add rule,…
- Excel Compare Cell Values Between Two Sheets: MethodsTo compare cells in the same positions on two worksheets, enter =IF(January!A2=February!A2,"Match","Different") in a results cell, then fill…
- How to Delete a Single Page in Word SafelyTo delete a single page in Word, click anywhere on it, press Ctrl+G on Windows (Option+Command+G on Mac),…
- How to Delete a Page in Word on Windows: Step by StepTo delete a page in Word on Windows, press Ctrl+G, enter \page in the Go To box, press…
- How to Hide Page Breaks in Excel and Keep Print LayoutTo hide page-break lines in Excel for Microsoft 365 on Windows, open File > Options > Advanced, find…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.