Making Multiple Selection Dropdown List in Google Sheets

The main objective of having a dropdown list in Google Sheets is to provide options that users can choose from. Since a dropdown list in Google Sheets gives a clear picture of what’s available, it ensures that users select only the items that are allowed.

What’s particularly great about a dropdown list is that it ensures there are fewer errors, as the users have the option to choose from a predefined list instead of manually typing the cell content.

With Google Sheets, you get to use the dropdown list functionality with ease. And guess what? It only takes a few clicks to make a single-cell dropdown or populate an entire row and column with dropdown lists.

Unfortunately, in Google Sheets’ default dropdown list, users can only select a single item from the dropdown list, which may not suffice in a situation where you are required to select multiple options in a dropdown list.

Let’s assume for a minute there is a list of colors you need to choose from and you have more than one color you are interested in. Or there is a list of coding languages you’re proficient in. In such an instance, it’s very possible that you know more than one, and there is a need to select multiple options from the dropdown list.

In such a scenario, having multiple selections in the dropdown list will come in pretty handy. Unfortunately, Google Sheets doesn’t have an option for this, as you are only allowed one option at a time.

Thankfully, we have found a way around this. It is absolutely possible to make a dropdown list in Google sheets that allows multiple selections using Google Apps Script.

Today’s Google Sheets tutorial will show how you can seamlessly create a dropdown listing in Google Sheets that allows for multiple selections. But before we get into the practical aspect of this guide, let’s start with the basics.

Let’s kick off this Google sheets tutorial by creating a dropdown list from a list of color options.

Allowing multiple selections in a dropdown list

Allowing multiple selections in a Google Sheets dropdown list can be tricky, especially if you haven’t done it before. But guess what? After reading the step-by-step guide we will show you shortly, you should be able to do it easily.

For this tutorial, we will use the following sample data to create a dropdown list in Cell C1.

To be able to create a dropdown list that supports multiple selections, you need to do two things.

First, you need to make a dropdown list using a list of items
After that, we will add the function in the Script Editor. Doing this will enable multiple selections in the dropdown list.

Now that you know what you need to do to create a dropdown list in Google sheets, let’s look at each step in detail.

Creating the dropdown list

Let’s assume we have a data set in a spreadsheet like the one displayed below, and we want to create a dropdown list in Cell C1. Here is how to go about it.

Step 1: Select Cell C1

Since we want to create a dropdown list in cell C1, we will go ahead and select that on our spreadsheet.

Step 2: Data > Data Validation

With cell C1 highlighted, head over to the Data tab and select the Data Validation option.

Step 3: Tweak data validation rules

After selecting the option for Data validation, a data validation rules window will pop up on the extreme right of your spreadsheet. Here, you’ll need to select the “Add rule” option and select the Cell range that reflects the cell where you’d like to have the dropdown.

Step 3.1: Tweak data validation rules

In the option for Criteria, select “Dropdown (from a range).” After that, select the range that has items you’d like to include in the dropdown. For this guide, we will use cell A2:A19 as our range.

Step 4: Click on Done

With your rules now set, click on Done. Your dropdown list should now appear in cell C1. Try to click on the arrow to see if you can see a list of options.

Here is a short video demo showing you our dropdown list.

If you look closely at our dropdown list, you’ll notice that you can only select one item at a time.

But since our goal is to enable multiple selections in a dropdown list, we will need to convert our current dropdown list, which allows only one option to be displayed in the cell, to one that allows for multiple selections.

To do that, we need to add a function script using Google Sheets Apps Script editor. This is where things get a little tricky and complicated.

Using Google Apps Script to enable multiple selections

Converting our dropdown list in Google to one that enables multiple selections isn’t as complicated as you imagined it to be. And with us guiding you every step of the way, you should be able to do it without sweating it.

To use Google Apps Script to enable multiple selections in a dropdown list in Google Sheets, you’ll need to use a unique code. For this Google Sheets tutorial, we will be using the below script code.

function onEdit(e) {

var oldValue;

var newValue;

var ss=SpreadsheetApp.getActiveSpreadsheet();

var activeCell = ss.getActiveCell();

if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=="Sheet1") {

newValue=e.value;

oldValue=e.oldValue;

if(!e.value) {

activeCell.setValue("");

}

else {

if (!e.oldValue) {

activeCell.setValue(newValue);

}

else {

activeCell.setValue(oldValue+', '+newValue);

}

}

}

}

So we suggest you copy and paste the code into the script editor, following the steps we will show you shortly.

Here, check out the steps to add the above script code to Google Sheets’ back end. Doing this will enable multiple selections in the dropdown list we created earlier.

Step 1: Extension > Apps Script

The first thing we want to do is head over to the Extension menu. From the options available, select Apps Script. This action will launch the Script Editor window.

Step 2: Enter Script editor

Remember the script code we mentioned earlier? Well, here is where you need to use that code. When the Script editor launches, you’ll notice a function in the Code.gs window. Go ahead and delete everything there. After deleting, copy and paste the script code we showed you earlier.

In case you’re confused, the video below will guide you.

Step 4: Save the code

After entering the script code, you’ll need to save the code. So head to the toolbar and hit the save icon.

Step 5: Name your project

If you look at the top part of the Script editor, you’ll notice our project is untitled. So we need to give a name to our project. To do this, click on “Untitled project.” A small box should pop up where you’ll enter the name. For this guide, we named our project “Multiple selections dropdown list.”

Step 6: Test selection

With everything now entered, it’s time to test the selection. You can do this by heading over to your spreadsheet and trying to select multiple options from the dropdown list. If you did everything right, you should be able to select multiple options from your dropdown list.

When selecting multiple options from the dropdown list, you’ll notice that there is sometimes a two seconds delay before the selected items appear. So don’t panic if, after making your selections, the changes don’t reflect immediately.

Note: When making your selections, you’ll sometimes see a red triangle at the top-right part of the cell. It may look like an error. But don’t worry, just ignore it, as everything should be working fine.

Let us also quickly add that the code we have inputted in the Script editor allows us to select the same item twice. For instance, if you select Orange and then select Orange again, it will show twice in the cell.

If you want to create a multiple selections dropdown list in Google Sheets that prevents multiple selections, you’d have to use a different code from the one we used earlier.

Allowing multiple selections in a dropdown list (without repetition)

As we mentioned earlier, the example we did earlier allows users to select multiple selections in a dropdown list with repetition. However, there are some situations where you wouldn’t want repetition. If that’s the case, you have to use a different script code.

Here is the script code that will allow you to enable multiple selections in a dropdown list in Google Sheets without repetition.

function onEdit(e) {

var oldValue;

var newValue;

var ss=SpreadsheetApp.getActiveSpreadsheet();

var activeCell = ss.getActiveCell();

if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=='Sheet1') {

newValue=e.value;

oldValue=e.oldValue;

if(!e.value) {

activeCell.setValue("");

}

else {

if (!e.oldValue) {

activeCell.setValue(newValue);

}

else {

if(oldValue.indexOf(newValue) <0) {

activeCell.setValue(oldValue+','+newValue);

}

else {

activeCell.setValue(oldValue);

}

}

}

}

}

If you look closely at the code above, you’ll notice that we are using cell C1 in the worksheet Sheet1 as an example. If your worksheet houses the dropdown in a different cell or sheet, you need to tweak the code to reflect that.

Here is the part of the code that makes it possible for it to ignore any repeated value in our dropdown list:

if(oldValue.indexOf(newValue) <0) {

activeCell.setValue(oldValue+', '+newValue);

}

else {

activeCell.setValue(oldValue);

}

The indexof() function in our code checks if the string in oldValue contains the string in newValue.

If it does, what happens is that it will return the index of the string in oldValue. If that’s not the case, it will return a value less than 0.

Let’s simplify it so you know exactly what we mean.

if the newly selected option from the dropdown list exists in our list, we would like to leave the list as it is (so we populate cell C1 with the previous value). On the flip side, if the newly selected option isn’t on our list, we want to add the newly selected option to our dropdown list with a comma (‘,’) and have it displayed in cell C1.

Multiple selections in dropdown (whole column or multiple Cells)

From the examples we have covered, what we showed you is how to get a multiple selection dropdown in a single cell. But guess what? You can get multiple selections for an entire column or multiple cells. And you don’t need to do much. All you need to do is make some minor tweaks to the code.

Multiple selection dropdown for whole column

If you want your dropdown list in google sheets to allow selection of multiple items in the entire column C, you’ll need to replace the below line of code:

if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=="Sheet1")

With the code below:

if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()=="Sheet1")

When you enter the code above, what you’re basically doing is checking whether the column is 3 or not. Essentially, any cells that are in Sheet 1 and Column 3 would satisfy the IF criteria. And should you have a dropdown there, it will enable multiple selections.

Again, if you’d like to enable this for the entire column C and F, you have to use the below line instead of the one we mentioned earlier.

if(activeCell.getColumn() == 3 || 6 && ss.getActiveSheet().getName()=="Sheet1")

The code above uses the OR condition within the IF statement. What the code attempts to do is check whether the column number is 3 or 6. Assuming a cell that has the dropdown is in column C or F, it would automatically enable multiple selections for those columns.

Multiple selection dropdown for multiple cells

If you want to enable multiple selections for multiple cells, you only need to make a few changes to the code outlined above.

In a nutshell

Making multiple selection dropdown lists in Google Sheets is pretty straightforward. Even though there are a few technical aspects to getting this done in Google Sheets, particularly when it comes to entering the code in Google Apps Script, we have attempted to simplify the entire process in today’s Google Sheets tutorial.

If you ever need to create a multiple selections dropdown list in Google Sheets, we are sure you won’t go wrong following the steps outlined in today’s tutorial.

To make your job even easier, we ensured we included multiple screenshots and short video demos. In case you ever run into a brick wall when creating a multiple-selection dropdown list in Google Sheets, feel free to check these resources out for guidance.

Sure, we know that navigating Google Sheets can sometimes be challenging. So in case you need clarifications or have certain questions you want answered, feel free to leave a comment, and we will get back ASAP.