Multi-Select Option in Data Validation in Google Sheets

Exploring The New Google Sheets Data Validation Menu Functionality

Google Sheets has introduced a significant enhancement to its Data Validation functionality the ability to select multiple options from a dropdown list. This feature is particularly useful for scenarios where users need to choose more than one item from a predefined list, such as in project management, inventory tracking, or any other use case that involves multiple selections.

However, this functionality is only available when using the “Chip” display style in the Data Validation settings, and not with the “Arrow” or “Plain Text” options.

New Google Sheets Functionality Multi-Select Option in Data Validation

In this article, we will explore this new feature, including how to create a dropdown menu with Data Validation, apply it to a range, and customize it using advanced options. We will also discuss how to create a Data Validation dropdown that sources data from another tab and includes the capability to run formulas and App Script functions.

How to Create a Dropdown Menu with Data Validation

Step 1: Apply Data Validation to a Range

1. Open Your Google Sheet: Start by opening the Google Sheet where you want to apply Data Validation.
2. Select the Range: Highlight the range of cells where you want the dropdown menu to appear. For example, if you want to apply it to cells B5 to B10, select these cells.
3. Access Data Validation: Go to the menu and click on Data, then select Data Validation.

Step 2: Select Criteria

1. Criteria Selection: In the Data Validation window, you’ll see a section labeled “Criteria”. Here, you can choose the type of data you want to allow. For a dropdown menu, select List of items.
2. Enter List Items: In the “List of items” field, enter the items you want to appear in the dropdown, separated by commas. For example, you might enter Option 1, Option 2, Option 3.

Step 3: Advanced Options

1. Handling Invalid Data: Below the criteria section, you’ll find the “On invalid data” options. Here, you can choose between two radio buttons:
   • Show warning: This option allows users to enter values that aren’t in the list but displays a warning.
   • Reject input: This option prevents users from entering any value that’s not in the list.
2. Display Style: Under “Appearance”, you can choose the display style for your dropdown menu. The three options are:
   • Chip: This option displays selections as chips, enabling multiple selections. This is the only style that supports the new multi-select functionality.
   • Arrow: Displays a dropdown arrow, but only allows single selection.
   • Plain Text: Shows the selected item as plain text, also limited to single selection.

Creating a Data Validation Dropdown from Another Tab

Sometimes, you may want to source the dropdown menu items from a different tab in your Google Sheet. Here’s how to do it:

1. Set Up Your Tabs: Suppose you have two tabs—Info and Functions. In the Functions tab, you’ll define the items for your dropdown.
2. Enter Your Data: In the Functions tab, enter the items for the dropdown menu starting from cell A2 (e.g., A2: Select Function, A3: Function 1, A4: Function 2, A5: Function 3).
3. Apply Data Validation:
   • Go to the Info tab, select the range where you want the dropdown, and open the Data Validation window.
   • In the “Criteria” section, select List from a range.
   • Click on the grid icon, then select the range from the Functions tab (e.g., Functions!A3:A5).
4. Use the Dropdown: The dropdown menu in the Info tab will now dynamically populate with the items from the Functions tab.

Advanced Use: Running App Script Functions from Dropdown

A powerful feature of Google Sheets is the ability to run App Script functions directly from a cell. You can leverage this capability with Data Validation to create a dropdown menu that allows users to trigger scripts.

1. Set Up Function Names: In the Functions tab:
   • In cell A1, add a leading apostrophe (') followed by an equal sign to prevent the formula from executing ('=)
   • In cells A2, A3, and A4, enter the clean names of your App Script functions, such as createWebsitePage(B6), updateWebsitePage(B6), and deleteWebsitePage(B6).
2. Concatenate Function Calls:
   • In cells B2, B3, and B4, use the =CONCATENATE function to combine A1 with A2, A3, and A4, respectively, to form executable function calls. For example, B2 would have =CONCATENATE(A1, A2) resulting in =createWebsitePage(B6).
3. Use Data Validation:
   • In the Info tab, create a dropdown as described earlier, but reference the range Functions!B2:B4 for the criteria.
   • This dropdown will now contain executable script functions that users can select and run directly from the sheet.

Conclusion

The new multi-select option in Google Sheets’ Data Validation feature, available exclusively with the “Chip” display style, adds significant flexibility for users needing to select multiple items from a dropdown list. By combining this with other features like sourcing data from other tabs and triggering App Script functions, Google Sheets becomes an even more powerful tool for managing and automating data. Whether you’re managing a project, tracking inventory, or automating workflows, these enhancements make Google Sheets a robust solution for a wide variety of tasks.