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.
Contents
Video Tutorial
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.
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.
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!
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.
| Strict | Not Strict | |
| Owner/Manager | Can add existing select values or new select values. | Can add existing select values, new select values, or values not in the select list. |
| Below Manager | Can only add existing values from a list. | Can add values from the list, or values not in the select list. |
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
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.
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.