In Excel: a standard Data Validation list stores one value per cell, so multiple selection needs a workaround — most commonly a Worksheet_Change macro that appends each new pick to the existing text (desktop Excel, saved as .xlsm), or one checkbox or helper column per option combined with TEXTJOIN. Excel for the web cannot create, run, or edit VBA macros. Open the workbook in the Excel desktop app to work with macros.
On this page7
Create the normal list first: Data ▸ Data Validation ▸ Allow: List with your options.
Right-click the sheet tab, choose View Code, and add a Worksheet_Change procedure limited to the list range.
In it, turn events off, capture the new value, use Application.Undo to read the previous value, and write both joined with ", ".
Turn events back on (also in an error handler), then save the workbook as .xlsm.
Without macros: give each option its own column of checkboxes or Yes/No cells and combine the ticked ones with =TEXTJOIN(", ",TRUE,IF(C2:E2=TRUE,$C$1:$E$1,"")) (TEXTJOIN needs Excel 2019 or later; in versions without dynamic arrays confirm this IF-array formula with Ctrl+Shift+Enter).
What this does
Data Validation lists are single-selection by design: choosing a second item replaces the first. There is no setting in the Data Validation dialog that changes this. To collect several choices in one cell you need something that remembers the previous value — an event macro that runs after each pick and joins old and new values — or a layout that stores each option separately and combines them with a formula. The macro route keeps the familiar arrow but only runs where VBA runs. Read the code once before running it: note which sheets, ranges and files it touches and whether it deletes or overwrites anything. Macros run only in desktop Excel with macros enabled, and the code is kept only in a macro-enabled format such as .xlsm. Treat “multi select dropdown” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected dropdown shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Column B (B2:B100) has a list of Red, Green and Blue. With the macro below in the sheet's code module, picking Red and then Blue in B2 leaves "Red, Blue" in the cell. The macro: Private Sub Worksheet_Change(ByVal Target As Range) — if Target is one cell inside B2:B100, set Application.EnableEvents = False, store the new value, call Application.Undo to read the old value, write old & ", " & new (or just new when the cell was empty), then set Application.EnableEvents = True. Tags, skills and categories often need more than one value per row. Knowing that the standard list cannot do it saves time hunting for a setting, and choosing between a macro and a helper layout depends on where the workbook will be used. Run a new macro on a copy of the workbook first. Do not count on Undo to reverse what a macro changed; the saved copy is your way back.
In Google Sheets
Google Sheets does not run VBA. A macro written for Excel has to be rewritten in Google Apps Script (Extensions ▸ Apps Script), and an .xlsm file converted to Sheets keeps its cells but drops the code. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. For “multi select dropdown”, the checks are about the environment as much as the code: desktop Excel with macros enabled for this file, a macro-enabled workbook and a saved copy to fall back on. Run it once on test data, compare the result with what the code says it does, and only then run it on the real sheet.
Common mistakes
- Looking for a "multiple selection" option in the Data Validation dialog — there is none.
- Forgetting Application.EnableEvents = True in an error path, which silently stops every event macro in the workbook.
- Saving as .xlsx, which strips the macro.
- Expecting the macro to run in Excel for the web, the mobile apps or Google Sheets — VBA does not run there.
- Selecting the same item twice and getting it twice; add a check with InStr if duplicates matter.
Frequently asked questions
Can Excel's Data Validation allow multiple selections natively?
No. A Data Validation list holds one value per cell; multiple selection needs a macro or a separate layout.
Does the combined value break the validation rule?
Validation only checks typed entries, so a value written by the macro is kept even though "Red, Blue" is not in the list.
How do I count or filter cells with several selections?
Use a wildcard: =COUNTIF(B:B,"*Blue*") counts cells that include Blue among their selections.