00:00
/
00:00
Audio/video is not supported
Please TryRefresh
Play
Fullscreen
Click and hold to drag
I. Intro
You can use the dropdown list in Sheets to standardize data input and improve input efficiency. There are two types of dropdown lists:
- Single option: Only one option can be selected per cell. For example, the Gender column in the image below.
- Multiple options: Multiple options can be selected per cell. For example, the Hobbies column in the image below.
250px|700px|reset
II. Steps
Add a dropdown list
Open the spreadsheet and select a cell or cell range. Click Dropdown from the toolbar, enter content for the options, and click Confirm.
Note: The options can't contain any commas.
250px|700px|reset
Edit options
- Switch between single and multiple options: You can switch between single and multiple options from the settings panel of the dropdown list.
Note: If you switch from single option to multiple options in a cell, you will not be able to input or edit formulas in the selected cell. If you switch from multiple options to single option in a cell, only the first option selected will be retained in the cell.
250px|700px|reset
- Set option color: In the settings panel of the dropdown list, select Option color and color icons will appear on the left side of each option. Click a color icon to change the color for the option.
250px|700px|reset
- Modify, add, and delete options: You can modify, add, and delete options in the settings panel of the dropdown list. You can also drag the ⋮⋮ icon on the left side of each option to rearrange the order of the options. If you select the Apply to exact same drop-down lists at the bottom of the settings panel, the settings you configured will also apply to all identical dropdown lists in the sheet.
Note: When you delete an option, the corresponding option selected in the cell will turn into text, and a red mark will appear in the corner of the cell to indicate a value in the cell is no longer a valid dropdown option.
250px|700px|reset
- Add multiple options at once: Click More settings in the upper-right corner of the settings panel and select Settings. For Source, you can select Custom to directly enter the options, separating the options with a (,). You can also select From a range and click the Grid icon to select a range that you want to add as options. Click Confirm to save the settings.
Note: If the data in the reference range is modified or deleted, the selected options in the cells will turn into text and a red mark will appear in the corner of the cell to indicate a value in the cell is no longer a valid dropdown option.
250px|700px|reset
250px|700px|reset
- Add alert and help text: Click More settings in the upper-right corner of the settings panel and select Alert and help text to set alerts for invalid data input and help texts when a cell is selected.
- The default option for When data is invalid is Reject input, which means users won't be able to enter any value in the cell and can only select options from the dropdown list. If you select Show alert, users can enter values that are not options from the dropdown list, but a prompt will appear stating that the value is invalid.
250px|700px|reset
- You can also select Show help text to enter a message that you want users to see when they select a dropdown cell.
250px|700px|reset
250px|700px|reset
Use the dropdown list
- Select options: Select a dropdown cell, search for the option, and select the option from the search results.
250px|700px|reset
- Remove the selected option: For single option dropdowns, select the cell and press Delete. For multiple options dropdowns, double-click the cell, and de-select the option from the list. You can also right-click the cell, and select Clear > Clear Values to remove all selected options in the cell.
250px|700px|reset
- Remove the dropdown list: Select a dropdown cell, click Dropdown from the toolbar, and click Remove Dropdown in the lower-left corner of the panel. After the dropdown list is removed, the selected options will remain in the cells as text. Select Apply to exact same drop-down lists at the bottom of the settings panel if you want to delete identical dropdown lists in the sheet as well. To clear both the drop-down list and the text in the cells, right-click the cells and select Clear > Clear All.
250px|700px|reset
III. FAQs