Dropdown Lists in your Spreadsheet

Multiselect Dropdown

Spreadsheet creators may want to use a dropdown list to limit the choice of values in a spreadsheet cell. You could do this to reduce typos or help the user narrow down their options.

Historically, you had to do this through a cumbersome process called data validation. Data validation is when the spreadsheet creator applies rules to a cell or range of cells. These data validation rules are not specific to creating dropdowns, so the interface is not streamlined. After building a data validation rule and specifying that the choices should be in a dropdown, you could still only allow one at a time.


Video Tutorial

Video showing how to use dropdown lists

Native Support for a Dropdown List

Spreadsheet.com has native support for dropdown lists. Further, it allows two different types of lists – Select and Multiselect. Yes, you heard that right. Mutliselect allows more than one choice at a time while still limiting what is available. Get a copy of the 🔗template to follow along and be sure to make your own copy.

The Select Data Type

To illustrate, let’s start with the single selection data type called Select. In this case, we will establish a header row and designate the column called Item Color as the Select data type. The select data type makes sense for this column because each cell should have only one value. An item cannot be two colors at the same time.

Updating a data type to Select
Select Data Type

When you are setting the data type, you will notice a checkbox with the value Strict. If you choose Strict, spreadsheet.com will delete all existing data that does not match one of the Select values. If you do not check Strict, all current values will stay.

Setting Values in your Dropdown List

A select data type needs to know what inputs it should accept. This menu of options will be what appears in the dropdown menu.

Three options for a dropdown list
Colorful Select Values

You can edit the select values at this point, and you can control their color. The example we’re using just so happens to be the name of colors so let’s make the colors match!

Column of items with some misspellings pointed out
Non-Strict Select Data Type

If you choose not to use the strict option for your dropdown list, any values you haven’t specified as select values will remain, as shown above. You can tell which ones they are because they don’t have a rounded rectangle behind them. This background coloring is an excellent reason to make the White value’s background a shade of grey instead of white. The background coloring allows you to see the rectangle against the white background.

If you wanted to change all items to the select data type, check the strict option. When you apply this data type, the spreadsheet deletes data if it doesn’t fit one of the options.

Permission Level Differences

The permission levels of the spreadsheet user also impact the behavior of the select data type. Also, Select data types do not always mean that you cannot add different values. It depends on the Strict toggle setting and the permission level of the user.

StrictNot Strict
Owner/ManagerCan add existing select values or new select values.Can add existing select values, new select values, or values not in the select list.
Below ManagerCan only add existing values from a list.Can add values from the list, or values not in the select list.
Different permission levels

The chart above illustrates the different ways in which the Select data type will limit the spreadsheet user’s ability to enter data. For example, if you have a permission level of Editor and the select data type is strict, you can only add predefined values in the dropdown list of Select values.

Multiselect Data Type

Menu showing options to build a dropdown list

The multi-select dropdown gives the user the highly coveted ability to choose one or more values for a cell. This flexibility increases the use cases for select data types as now you can apply them to situations where you may need more than one value. The menu that you use to set the values looks similar to the select menu.

A finished dropdown with background colors
Multiselect Dropdown

Dropdown Lists Solved!

In summary, between the select and multi-select data types, users of spreadsheet.com can quickly implement a dropdown list in their spreadsheets without working through data validation.

1 comment

Leave a comment

© 2020 – 2023 learnspreadsheet.com