How to Make Multiple Selection Drop-Down Lists in Google Sheets

The default Google Sheets dropdown list allows the user to only select a single item from the list. I often want to select more than one option in a drop-down list. For example, when there’s a collection of colors to choose from, I might like more than one color. Or, in a document where we list proficiencies, I might want to choose more than one from a list of coding languages. So how do you do this?

Let’s start from scratch. I’ll begin by creating a fresh dropdown list from a list of color options. If you’re not already sure how to add a drop-down list in Google Sheets, start with this quick guide. Once you have one in your spreadsheet, we can move into App Script to modify the code.

If you want to save time: Make a copy of my template. It’s the spreadsheet I made in Google Sheets where multiple selections are already enabled. Note that you’ll need to make a copy to use it.

Allowing Multiple Selections in a Dropdown list (without Repetition)

For this tutorial, I will use the following dataset of items and will create the drop-down in cell C1. Note that this differs from a dependent drop down list. Here, we focus on allowing the user to choose multiple items. A dependent drop down list changes what’s shown based on a user’s choice.

Ready to see how to use the Google Sheets multi-select dropdown? Here’s how the data set looks right now.

To create a drop-down list that allows multiple selections, you need to do two things:

1. Create a drop-down list using a list of items
2. Add the function in the Script Editor that will enable multiple selections in the drop-down.

Let’s look at each of these steps in detail.

Creating the drop-down list

Suppose I have a dataset of items (shown below) and I want to create a drop-down list in cell C1.

Below are the steps to do this:

1. Select the cell where you want the drop-down list
2. Navigate to Data >Data validation
3. In Criteria, select Dropdown (from a range) and then select the range that has the items that you want to show in the drop-down.
   
4. Open the Advanced options and make sure Show a warning is selected instead of Reject input (this is part of allowing multiple inputs, you don’t normally have to do this)
   
5. Click on Save

Your dropdown will now appear in your designated cell (C1 in this example). When you click on the arrow you will see your list of options.

Notice now that you are only allowed to select one option at a time.

Now let me show you how to convert this drop-down (which allows only one item to be displayed in the cell) to the one that allows multiple selections.

And to do that, you need to add the function script in the Google Sheets script editor. Not sure how to use it? I covered how to use the Google Sheets script editor in more detail. It’s the next step in my guide on how to create drop down list in Excel with multiple selections. If you already know how to use it, move on to this next step.

Adding the Google Apps Script to Enable Multiple Selections

Below is the script code that you would have to copy and paste in the script editor. If you need additional guidance, I provide more steps in the below section (after the code):

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell();
  var sheet = ss.getActiveSheet();

  // Only run on cell C1 of Sheet1
  if (activeCell.getColumn() == 3 && activeCell.getRow() == 1 && sheet.getName() == "Sheet1") {
    var newValue = e.value;
    var oldValue = e.oldValue || ""; // If no old value, treat as an empty string

    // If the new value is empty (cell is cleared)
    if (!newValue) {
      activeCell.setValue("");
    } else {
      // If old value exists, append the new value, ensuring no duplicates
      if (oldValue) {
        var values = oldValue.split(",").map(function(item) {
          return item.trim();
        });
        
        // Only add the new value if it's not already in the list
        if (values.indexOf(newValue) === -1) {
          values.push(newValue);
        }
        
        activeCell.setValue(values.join(", "));
      } else {
        activeCell.setValue(newValue);
      }
    }
  }
}

Below are the steps to add this script code in the Google Sheets backend so that the drop-down we created in cell C1 can allow selecting more than one option:

1. Navigate to Extensions > Apps script
2. In the Code.gs window, remove anything that is already there and copy and paste the above macro code
3. 
4. Click on the Save button in the toolbar (or use the keyboard shortcut Control + S)
5. Click Run

Now come back to the worksheet and try making multiple selections in the drop-down. For example, first, select Apple and then select Banana.

You will notice that it takes a second (sometimes two seconds) and will then show you both the selected items (separated by a comma).

Note: You will see a red triangle at the top-right part of the cell. It may look like an error (which it is, as the value you have in the cell is not what it expects), but you can safely ignore this.

Note that this code doesn’t allow you to select the same item twice. In a previous version of our code, clicking apple more than once would add it more than once to the selections.

How does the code work?

Let’s try to understand this code part by part.

The code starts with the line

function onEdit(e)

onEdit() is a special function on Google Sheets. It is also known as an event handler. This function is triggered every time there is a change in your spreadsheet.

We want our multiple selection code to run every time an item is selected from the dropdown list, so it makes sense to put our code in the onEdit() function.

Now, the AppScript passes this function as an event object as an argument. Typically, the event object is called e. This event object contains information about the event triggered.

If you know the basics of AppScript, you will find the first four lines quite easy to understand:

var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();

I have declared two variables – one (oldValue) that will hold the old value of the cell and another (newValue) that will hold the new value of the cell.

The variable activeCell will hold the currently active cell that has been edited.

Now, we don’t want the code to run every time any cell is edited. We only want it to run when cell CA1 of Sheet1 is edited. So we make sure of that by using an if statement:

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

The above code checks the row and column number of the active cell and the sheet name. Since our drop-down is in cell C1, it checks whether the row number is 1 or not and whether the column number is 3 or not.

Only when all these three conditions are met that the code within the IF statement is executed.

Below is the code that is executed when we are on the right cell (C1 in our example)

newValue=e.value;
oldValue=e.oldValue;

e.oldValue is also a property of the event object, e. This holds the previous value of the active cell. In our case, this would be the value before we make the drop-down selection

We want to assign this to the variable oldValue.

e.value is a property of the event object, e. This holds the current value of the active cell. We want to assign this to the variable newValue.

First, let us consider what happens if no option is selected. In that case, e.value will be undefined. When this happens, we do not want anything displayed in cell A1. So we put a blank value on the cell.

This will also be the case if the user decides to delete all previous selections and restart from scratch.

if(!e.value) {
activeCell.setValue("");
}

If the user does select an option, then the lines following the else statement will be executed. We now want to specify what to do if an option is selected for the first time from the drop-down list.

That means e.oldValue is undefined. When this happens, we want only the selected option(newValue) to be displayed in cell A1.

if (!e.oldValue) {
activeCell.setValue(newValue);

Finally, we specify what to do the next time onwards that an option is selected. That means when both e.value and e.oldValue hold specific values.

else {
activeCell.setValue(oldValue+', '+newValue);
}

Once you are one typing the code, save it and then try making multiple selections from your dropdown list. You will find all your selected options displayed one by one, separated by commas.

Mistakes, and What to Do

If you make a mistake, you can always clear the cell and start over. When this happens, we want to display both the previous values and the newly selected value in cell A1, all separated by commas.

Note: When you use the above code, it will not allow you to go back and edit part of the string. For example, if you want to manually edit the item string or want to delete a part of it, you won’t be able to do this. You will have to delete all the cell content and start over if you want to make any changes.

Below, I have provided details of how you can make changes to your code to make sure an item can only be selected once so that there are no repetitions.

Allowing Multiple Selections in a Dropdown List (with Repetition)

Below is the code that will allow multiple selections in the drop-down without repetitions.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell();
  var sheet = ss.getActiveSheet();

  // Only run on cell C1 of Sheet1 (or any specific range you want)
  if (activeCell.getColumn() == 3 && activeCell.getRow() == 1 && sheet.getName() == "Sheet1") {
    var newValue = e.value;
    var oldValue = e.oldValue || ""; // If no old value, treat as empty string

    // If the new value is empty (cell is cleared)
    if (!newValue) {
      activeCell.setValue("");
    } else {
      // If there's an old value, append the new value (without checking for duplicates)
      if (oldValue) {
        var values = oldValue.split(",").map(function(item) {
          return item.trim(); // Remove extra spaces
        });

        // Append the new value (even if it's already in the list)
        values.push(newValue);

        activeCell.setValue(values.join(", "));
      } else {
        activeCell.setValue(newValue);  // If it's the first value, just set it
      }
    }
  }
}

In the above code, I am again using the cell C1 in the worksheet Sheet1 as an example. In case your drop-down is in a different cell (or sheet), you need to adjust the code accordingly. For example, if you were using D2 you would change the sixth line of code to:

if(activeCell.getColumn() == 4 && activeCell.getRow() == 2 && ss.getActiveSheet().getName()=='Sheet1') {

D2 is the forth column and second row.

Updating an Old Reference in the Apps Script Editor

In a previous iteration, we highlighted the repeated value in the drop-down with the following code.

// Only add the new value if it's not already in the list
if (values.indexOf(newValue) === -1) {
  values.push(newValue);
}

In the version that allows repetition, The indexof() function checks if the string in oldValue contains the string in newValue.

If it does, then it will return the index of the string in oldValue. If not, it will return a value less than 0.

If the newly selected option does exist in our list, we want to leave the list as it is (so we populate cell C1 with the previous value). If not, then we want to add the newly selected option to the list with a comma (‘, ’) and display it in cell C1.

That led to an error where you couldn’t choose an item with any part of the string used in your other selection. A reader mentioned they wanted to select a small size and an extra-small size from a menu where XS, S, M, L, and XL were available. The old code prevented that from happening because S showed up in XS.

This updated code adds a comma-separated string of values into an array, so you can manipulate each value individually.

Multiple Selection in Drop Down (Whole Column or Multiple Cells)

In the above examples, I have shown you how to get a multi-selection drop-down in a cell.

But what if you want to get this in an entire column or multiple cells.

You can easily get this done with some minor modifications in the code.

If you want the drop-down to allow selecting multiple items in the entire column C, you need to replace the following line of code:

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

with the following line of code:

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

When you do this, we are only checking whether the column is 3 or not. Any cells that are in Sheet1 and in Column 3 would satisfy this IF criterion and there any drop-down in this would allow multiple selections.

Similarly, if you want this to be available for entire column C and F, use the below line instead:

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

The above line uses an OR condition within the IF statement where it checks whether the column number is 3 or 6. In case a cell that has the drop-down is in column C or F, multiple selections would be enabled.

Similarly, if you want this to be enabled for multiple cells, you can do that as well by modifying the code.

So this is how you can enable multiple selections in a drop-down in Google Sheets. While this is not available as an in-built feature, you can easily do this with some Google Apps Script magic.

I hope you found this tutorial useful! For more Google Sheets and Excel hacks, visit SpreadsheetPoint.com.