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.
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. Before you run it on a workbook other people depend on, try it on a copy or a few rows first. Undo only reaches back through the current session, so a quick trial run is the cheapest way to see exactly what will change before the file is saved and shared. For “excel data validation multiple selections”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. 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 is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.
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. When the result is not what you expected, undo straight away rather than repairing it by hand — undo restores the sheet exactly, while manual fixes tend to leave small inconsistencies behind that surface later.
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. If you take one thing from this page on “excel data validation multiple selections”, make it the order of checks rather than the individual clicks: confirm what is selected, apply the step, and look at the result before moving on. That small routine is what keeps Excel work predictable when the same task comes back in a slightly different workbook.
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.