The drop down menu feature in Google Sheets is useful for efficient data entry. It can also be used to create dynamic spreadsheets, for example, dashboards that change based on choices a user makes.
At the end of July 2024, Google announced multiple selections in drop downs (scheduled to roll out in late-August/early-September). This is great news that will give us even more flexibility in our spreadsheets.
In this post, we’re going to take a look at this new drop down feature:
How to Enable Multiple Selections in Drop Downs
To enable multiple selections, create a regular dropdown and then check the box that says “Allow multiple selections”:
Boom! 💥 That’s it.
Working with Drop Down Multiple Selections in Formulas
If you select multiple options from a drop down menu, the output is a comma separated list:
To work with them in formulas, we use the SPLIT function to separate the choices into their separate parts.
Note the space after the comma. We need to be mindful of this in our formulas. Split out the comma-separated lists with this formula, which accounts for the space too:
=SPLIT(B2,", ",FALSE)It looks like this in our Sheet:
We can use a BYROW function to expand this SPLIT formula to work with a range of multi dropdowns. This single formula in cell C2 splits out all the cells in the range B2:B6 and outputs all the data in C2:E6 range:
=BYROW(B2:B6,LAMBDA(r,SPLIT(r,", ",FALSE)))In our Sheet:
Finally, we could wrap this with a TOCOL function and a QUERY function to count our choices:
=QUERY(TOCOL(BYROW(B2:B6,LAMBDA(r,SPLIT(r,", ",FALSE)))),
"select Col1, count(Col1) where Col1<>'' group by Col1 order by count(Col1) desc label Col1 'Option', count(Col1) 'Count'",0)In our Sheet:
