The Multiselect data type allows you to set the possible options for a cell, column, or range of cells. Multiselect data types act like drop-down lists and are useful when you frequently repeat a similar alphanumeric entroes (for instance, ingredients on a sandwich, "Lettuce", "Tomato", "Cheddar", etc) or for when you wish to restrict your users to a set of options.
As an example, if you were using Spreadsheet.com for bug tracking, you might have a column to note where the bug is visible to the public, and might frequently repeat the options: "Web Browsers," "Mobile Browsers," and "Mobile App." Setting these as options would allow your users to choose from them, selecting multiple options if the bug appears in multiple places. You might even wish to restrict requests to those options so that no one can misunderstand this selection and type that the bug is visible "when I log in" or "on my screen." Multiselect data types also allow you to set background colors for each option, making them highly customizable.
How to set up a Multiselect data type
To set a column, cell, or range of cells to a Multiselect data type, simply right click the desired cell, column, or range and choose Edit data type.... From there, an update dialog box will appear, with the data type drop-down, where you can choose the Multiselect data type . See our article on changing data types for more detailed information on other ways to change to a Multiselect data type.
Once you've chosen the Multiselect data type, you can define options for your select list by clicking + New Option , and typing the option name.
Once you've set up the Multiselect data type and set the options, users can either type an option into a cell or choose from the option drop-down list.
To remove a chosen option from a cell, simply delete or click on the option in the cell and click the black X. For instance: .
Users with Owner or Manager permissions will also have the ability to Add options in cells. When typing in currently non-existent options, owners or managers will see the + Add Option appear, like this: .
Note: With the Multiselect data type, users can select multiple options. If you'd like to restrict them to only one option, please see our article on the Select Data Type.
Converting Existing Data to Multiselect Data Type
If you are converting existing data to a Multiselect data type, existing cell entries will appear as separate options. Also, entries separated by a comma will become separate options in your Multiselect list. For instance, in the animation below, cells containing where a bug appears are converted to Multiselect, turning cell entries (like: "Web Browsers-All," "Mobile Browsers," and "Mobile App") into options in the multiselect list and assigning existing cells those options.
Like all other data types (except Automatic), you can enable Strict cells or columns for the Multiselect data type. Setting cells as Strict will ensure that cells contain only the options you've chosen and no other data.
Note: When converting data to Multiselect, choosing Strict will empty any cells containing data that does not match your select options. Of course, you can always revert by clicking undo in the toolbar or with the keyboard shortcut Ctrl + Z (CMD + Z for Mac users).
One unique property of Multiselect and Select data types is the ability to set different colors for each option, useful for quickly visualizing your data.
To change the assigned color for an option, right click on the cell, column, or range of cells that the select list appears in, and select Edit data type...
From the dialog box that opens, first make sure the color option is checked in the select update column menu:.
Then you can click on the paintbrush next to each option, for instance: . Clicking on the paintbrush will open a selection window like the following:
Sorting, Rearranging, and Deleting Options
You can also sort your options by clicking the Sort button in the update data type dialog box to sort either alphabetically or numerically (depending on your option names). Or you can rearrange the sort manually, by dragging and dropping options via the sort selector (next to each option name). You can also delete options via the X to the right of the option name .
Note: Editing the name of the option will change the data in cells currently assigned that option. For instance, changing the name of the option "High" in the animation above to "Urgent" would change all cells that are currently marked as "High" to "Urgent" instead.