Follow the simple steps below to add a multi-select dropdown in Google Sheets.
If you need a dropdown list in cell C1 with several items, you can choose to write your dropdown options in the first column of your current sheet or on a different sheet. This step depends on how you prefer to organize your data.
Click on cell C1, where your dropdown will be located. Navigate to the Data menu and select Data Validation.
Here, click 'Add rule'.
In the 'Criteria' section, select 'Dropdown (from a range)' and indicate the range with your desired items.
Within the Data Validation settings, expand Advanced options. Ensure that you select 'Show a warning' instead of 'Reject input.' This setting is crucial to enable multiple item selections. After adjusting the settings, click 'Done'.
After clicking 'Done', your dropdown in cell C1 is now active. When clicked, it should show the options you've set up. Currently, this dropdown only supports selecting one option at a time.
To modify this into a multi-select dropdown, a Google Apps Script is necessary. Navigate to Extensions and then select Apps Script.
This opens the script editor for your Google Sheets document.
Clear any existing code in the editor and paste the following script:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName() == "Sheet1") {
var newValue = e.value;
var oldValue = e.oldValue;
if (!newValue) {
activeCell.setValue("");
} else {
if (!oldValue) {
activeCell.setValue(newValue);
} else {
activeCell.setValue(oldValue + ', ' + newValue);
}
}
}
}
Save this script (no need to run it). It's designed to trigger automatically when an edit is made in the specified cell.
Return to your sheet and try selecting multiple items from the dropdown in cell C1, such as 'Apple' followed by 'Banana'. You should see both selections in the cell, separated by a comma. If a red triangle appears in the cell corner, it indicates a standard alert for content mismatch in dropdown cells. You can ignore it in this case.
We hope that you now have a better understanding of how to create a multi-select dropdown in Google Sheets. If you enjoyed this article, you might also like our article on how to set up HLOOKUP on Google Sheets or our article on how to set up the percentage formula in Google Sheets.
