Brought to you by:

Hi Reader,

Happy Monday!

Welcome to the Google Sheets Tips newsletter #316, your Monday morning espresso, in spreadsheet form.

Today we're looking at a new feature in Sheets.

Google recently added a multi-select option to dropdown menus.

It's a welcome update that makes an already great feature even better. It adds another dimension to interactivity with Sheets and, when combined with the new tables format, makes our data much richer.

Hopefully, by now, you've all got access so this email is useful!

Brought to you by: Keeping Using a Google Group to manage your shared inbox? Keeping is the simple & streamlined shared inbox for Gmail. Add Keeping to your Google Group (like help@, hello@, or support@) to give it new superpowers. Keeping makes it easy to share private notes, assign and prioritize incoming emails, all without leaving Gmail. Set up is easy - simply add Keeping to your existing Google Group and install the Keeping Chrome Extension. See how hundreds of companies supercharge their Google Groups with Keeping. See How Keeping Works

➜ News

I.
​How a bipolar artist from Ghana is using a spreadsheet to help monitor his life:

​‘Bipolar, colour and me’ - an artist’s spreadsheet of emotion​

➜ Sheets Tip #316: Multi-select dropdown menus

Wow! This is exciting!

The already-wonderful dropdown chips in Google Sheets recently got a huge upgrade: they now work with multiple selections!

This is what it looks like in our Sheets:

We can now select more than one option from a list in a cell.

Hopefully, you're as excited as I am! 😃

It's easy to create a multi-select dropdown. We create a regular dropdown (here's how to do that) and then check the "Allow multiple selections" checkbox to enable this feature.

How to work with multi-select dropdowns

First, we must understand how multi-select dropdowns are presented in cells, before we can do anything with them.

Choose a cell where multiple choices have been selected and look at the data in the formula bar.

It's a comma separated list.

Another way to see this is to use an "=" sign pointed to a cell with a multi-select dropdown, e.g.

=B3

where the multi-select dropdown is in cell B3:

Working with Multi-Select Dropdowns

To work with this data we need to split it up into separate cells, because then we can do things like count the data for example.

To do this, use the SPLIT function:

=SPLIT(B2,", ",FALSE)

Two important things to note:

1. We're splitting with a comma AND a space ", " because the multi-select separates values by a comma and a space.
2. We need to include the FALSE argument, which prevents the SPLIT treating the comma and space separately. If we omit the FALSE, it splits "Option 1" into "Option" and "1", which we don't want.

With this in mind, let's create a formula that converts the multi-select into a single column, which lets us do things like count the values:

Here's the formula:

=SORT(TOCOL(SPLIT(B2:B6,", ",FALSE)))

Now we have a regular column of data that we can count for example. Use COUNTIF, the QUERY function, or even a pivot table to count the different entries.

For example, assuming the results of the SPLIT are in column D like the image above, then this QUERY formula will summarize the options:

=QUERY(D2:D10,"select Col1, count(Col1) group by Col1",0)

In our Sheet:

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. "My spreadsheet doesn't do that" Microsoft Excel ad from the 1990s!​