Suchi Nandi Puja
The primary function of drop down list in Google Sheets is to allow users to select from alternative options. It ensures that users can choose any alternative from a list of options. Dropdown lists can be used to let customers select any product from a list of various products and in many other areas. Unfortunately, you can not select multiple options in the same cell using a simple dropdown list. So we will explain how you can create a drop down list in Google Sheets for multiple selection in the same cell using Apps Script.
Step-by-Step Procedure to Create Drop Down List for Multiple Selection in Google Sheets
If you want to make a drop down list with multiple selections in Google Sheets like below you can follow the step-by-step procedure explained here. It will be really a great guideline for you if you are facing problems with multiple selections in your worksheet. Here, I tried to explain in the simplest way so that you can do it easily. Now, have a look at the sample image below to understand what will be the outcome of this article.
Step 1: Select Range for Drop Down List
- First, select cell D5 where you want to get the drop down list containing the alternative options.
Read More: Updating Cell Values Based on Selection in Drop Down List in Google Spreadsheet
Step 2: Apply Data Validation
- Then, go to the Data tab in the ribbon and click on the Data Validation option.
Read More: How to Edit Data Validation in Google Sheets (With Easy Steps)
Step 3: Select Criteria Range
- Then, the Data validation window will show up. Choose List from a range as the criteria. Next, click on the Select data range icon in the Enter a range or formula box.
- After that, the Select a data range dialog box will appear. Now select range B5:B12 which contains the alternative options and press OK.
- Next, check the checkbox for the Show drop-down list in cell option.
- After that, mark the radio button for Show warning. Now, press the Save button in the lower right corner of the dialog box.
- Then, when you return to your worksheet, you can see the normal drop-down list is created in cell D5. You can choose a single option at a time in this drop down list.
Read More: How to Use Data Validation in Google Sheets from Another Sheet
Similar Readings
- Dependent Drop Down List for Entire Column in Google Sheets
- How to Add Color to Drop Down List in Google Sheets (Easy Steps)
- How To Lock Rows In Google Sheets (2 Easy Ways)
Step 4: Use Apps Scripts for Multiple Selections
Following the steps above, you can make drop down list to choose only one value at a time. But if you want to select multiple options at a time in the drop down list, you need to use Google Apps Script.
- To set that up, first, click the Extensions tab in the ribbon and choose the Apps Script option there. It will open the Apps Script window.
Step 5: Write Code to Select Multiple Options
- Now, copy the code below using the copy button.
function onEdit(e)
{
if (e.range.rowStart != 5 || e.range.columnStart != 4) return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
- Then, paste the copied code in the code box, press the Save icon to save it, and then run the code.
Explanation of Apps Script Code
function onEdit(e)
The code will run only when you try to edit the sheet.
if (e.range.rowStart != 5 || e.range.columnStart !=4) return;
This limits the code to run only if you edit cell D5 whose row number is 5 and column number is 4.
e.range.setValue(e.oldValue + ", " + e.value);
It concatenates the previously selected options with the newly selected options and separates them by commas.
Step 6: Choose Multiple Options in Drop Down List
- After that, go back to your worksheet and select the cell where the drop down list you created before. Now, suppose you want to choose Banana along with Apple. So select the drop down arrow and click on the Banana option.
- As a result, you can see that both Apple and Banana are showing in the same cell. In this way, you can select multiple options in the same dropdown list.
Read More: Create Multiple Dependent Drop Down List in Google Sheets
Things to Remember
- The code will not work if you choose the Reject input option instead of the Show warning option for invalid data in the Data validation window.
- You can change the row and column numbers in the code as per your dataset.
Conclusion
In this article, we have shown how to create drop down list in Google Sheets to allow multiple selection. I hope, this article will be helpful for those regularly working on Google Sheets and using drop down lists for multiple selections. If you have any other solution to do this task please let me know through the comment section. Please visit or OfficeWheel website for exploring more about Google Sheets.
