Allow Multiple Selections is in Google Sheets Yay!

Beginner

About this Tutorial

Now you can select multiple options in a dropdown. Hip Hip Hooray!

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

FILTER() IFNA() JOIN() SEARCH() SPLIT() TRANSPOSE()

Video Transcript

<div>0:00 Finally, it's here. Ah, we have been asking for this feature for a long time. We have drop-down menus and we want to select multiple items in a drop-down menu. 0:10 Google Sheets now has it. So, this is rolling out, I think, completely for everyone and every user on September 5th. 0:20 So, if you're watching this before that, you may not have it. But, I do. And ah, September 5th everyone will have it, I do believe. 0:28 At least, that's what they say. Okay. So, have a look at the drop-down menu.

And, I will even show you a problem with them that may be fixed in the future. 0:37 But, also, I will show you a little bit of a workaround that is a little ick. But, it will work. 0:43 Ah, these drop-downs work and I'll show you how to get them right now.

So, we have ah, we'll start off with just blank cells . 0:53 Ah, and we have a bunch of planners and we want to make a party planning committee here for each of the major holidays coming up so normal Normally, you would right-click on a cell and just as you would create a drop-down, let's just create a normal drop-down. 1:07 We're going to change this to a drop-down from a range . You can use any drop-down if you want, if you want to add the options. 1:12 But, for our case, we know we're dropping down from a range . So, we'll select this range .

It's a, actually it's B2 colon B. 1:21 Click OK. It has all of our selections. And, we can color-code them. Color-code will show up in our multiple selections. 1:30 Let's do that. Let's just color-code each and every one, or most of these. There we go. Now, we're not done yet. 1:39 At the very bottom, it says allow multiple selections. Because, right now, we can only select one. But, if we click allow multiple selections, you see it changes the selection . 1:51 And, the way we select is by just clicking and checking off these items, or people, in this case.

If we click Advanced Options , we will notice that we must have a chip . 2:02 So, this does not work if you have arrow or plain text , or it doesn't even work to allow you to have arrow or plain text . 2:08 It will be a chip only for now. We can choose still to show warning or reject the input if the data is valid. 2:15 We're going to keep reject the input because I want to show you something that really irks someone. Actually, Magda found this from the group I Love Google Sheets on Facebook. 2:26 She found this sort of error or issue. I'm going to show it to you and how to fix it.

But let's look at this. 2:32 We have, uh, we can copy and paste this, and even if we delete the items in it, we can now select the items here. 2:40 Dale, Earl, and Fran are selected for Thanksgiving. We'll have Greg, Bart, and Ivan for Christmas. For New Years, let's select a few more. 2:52 And we have our selections. Great.

Now, what if, I'm going to show you a really big error here and a little bit of a waste workaround, but it's an issue. 3:03 So what if one of our people changes names, or they change people, like maybe between now, it's the end of August now, and between Christmas, Greg is no longer with the company. 3:16 We will change his name to Harold. If you notice, over here, it does not change Greg to Harold. So even though we have selected in our range , Harold, Greg doesn't exist anymore here. 3:31 But we have to go over here and change Greg to Harold. Here comes the issue.

We're going to just select Harold. 3:38 There's a problem. The data you entered violates the data validations that you have set. So just because Greg doesn't exist in our list means Greg, we can't even delete Greg from here. 3:52 So let me command Z this. So now it is correct. If I first take Greg off, now I'm going to change the name to Barry, let's say. 4:06 And I'll go back and select Barry. It's all fine. There is one other way we can do this.

Let's change it back to Greg and you'll see we have an error here and it's going to give us an error and we cannot delete Barry. 4:18 We cannot even copy and paste this. If we copy and paste it, it still has the error . But if you just select one click, not double click, but select one click the cell. 4:31 . And just hit delete. We now have to put in the other people, right? We had Ivan and Bart. But we don't have that error anymore. 4:42 Ivan, where was Bart? Ivan and let's say we're assigning Greg here, right? So that's a couple of workarounds.

One is make sure that we go and find where we're changing the name, take them out first, change the name in our range , and then go and add them again. 4:58 Or you can delete all of them. cells and add everyone back in. That is a bit of an issue, but it gives you a little bit of work around when you get that reject the input. 5:09 Also, um, let's try it. Instead of reject the input, let's have show a warning . And we're going to update this for everyone, e2, e5. 5:22 Done. So now all of these have just show a warning . And let's see if the same thing happens.

So we'll I'll see you change Carl to Barry. 5:35 We have an issue, right? We can't delete Carl from here. That same issue happens. But at least we don't get the total like message that gets in our face and prevents us from doing anything here. 5:49 Carl will be here. We can't delete Carl from here. That is an issue. So maybe you do want to add people back in and the Dale. 5:59 real. Or let's say. So again. That doesn't fix it. Just doing show a warning or reject the input. Copy and pasting. 6:10 Somewhere else doesn't help.

You must either delete the name from the list first or delete everyone from the list once you have an error . 6:20 Cool. But these multiple selections are really awesome. If you want to select this E2 cell, I want to show you what it looks like. 6:28 So if we just do E2. It's coming out as a comma separated set of values . So we can do things like split this text. 6:38 Actually we'll split this text by the delimiter is going to be this and we get all of the people. We can even transpose this. 6:47 Transpose to get a list of these people in.

The committee name here and the committee selection here. And let's say we have a list and we want to know. 6:59 Hey Andy. Which parties are you planning? We can do a filter . We can select the range is actually going to be the parties that we want in our cell. 7:12 And now we're going to select search. We're going to just search for b2 Andy. And the text we're going to search is the e column. 7:24 And what we get out of that is actually a list that looks like this. It is Halloween and New Year's. 7:30 But we can. and join the list with us, uh comma.

And now we have a list of every party someone's uh planning here. 7:48 We can add berry to a list. We can see berry didn't have one. If we have that f-n-a, f-n-a meaning no non-found we can say none found. 7:58 So let's see. Which one's Fran is doing, Fran is doing Thanksgiving. If Fran is not doing Thanksgiving. Let's say we see none found. 8:12 Because we put the if-n-a. So this is a bit of a complex formula but we are just filtering for anything where we find the name. 8:21 We then join them up because they're going to be an array.

And then we just say if-n-a meaning none of applicable or no non-found. 8:29 We actually write the word non-found here. You can write anything. . Anything you want. No committees. No parties. No party planning. 8:35 Anything you want. Let's see. None found for Hank. If we add Hank even to this one. And there he is in New Year's. 8:43 And we get a common separated value uh list of every committee they're on. Hopefully this has been very helpful for you. 8:51 Enjoy.</div>