Drop Down List in Google Sheets: Explained
In this article, you will learn what a pull-down list is and how to create it in Google Sheets.
What is a drop-down menu in Google Sheets?
In Google Sheets, a drop-down list is a feature that allows you to create a list of items that you can choose from. You can create a drop-down list in Google Sheets by using the Data Validation feature.
There are two types of drop-down menus, Drop-down list and Drop-down list (from range) depending on how to create it. We will explain how to create them in the following section. As an advanced tip, there is a list called a dependent drop-down list, whose items change depending on the selection in another drop-down menu.
When is a drop-down list beneficial in Google Sheets?
A drop-down menu in Google Sheets can be beneficial in a number of situations:
- When you have a list of items to which you want to limit the user's options. This can be helpful in ensuring that data is entered consistently and accurately, especially when the cell is referred to as a search keyword by a lookup formula
- When you want to make it easier for the user to enter data by providing a list of options that they can select from. This can be especially useful if the list of options is long or difficult to remember.
- When you want to enforce data integrity by ensuring that only valid data is entered into a cell.
- When you want to prevent errors caused by typos or misspellings.
How to make a drop-down list in Google Sheets
As mentioned before, there are two ways to make a pull-down list in Google Sheets. They are different in terms of how to input options, manual input, or cell reference. Thus, you can follow the same steps for the first couple of actions.
To create a drop-down list, follow these steps first:
- Select the cell or cells where you want to create the drop-down list.
- Click the "Insert" tab on the top menu and select the "Drop-down" button, which brings “Data validation rules” up on the right side.
- Alternatively, you can go to the “Data” tab in the menu bar and choose “Data validation”, which shows the same window on the right side.
If you want to enter options in a pull-down list manually, go to “Drop-down List” below and navigate to “Drop-down List (from range)” below if you want to use cell references to create items in the list.
Drop-down List
- Choose “Drop-down” in the “Criteria” section in “Data validation rules”
- Fill a textbox and change its color, if necessary
- Add text boxes as needed and repeat step 5 above until you enter all options
- Adjust “Advanced options” if necessary
- Click the "Done" button at the bottom of “Data validation rules”
Drop-down List (from a range)
- Choose “Drop-down (from a range)” in the “Criteria” section in “Data validation rules”
- Select a range containing items in the same file
- Change their color, if necessary
- Adjust “Advanced options” if necessary
- Click the "Done" button at the bottom of “Data validation rules”
Advanced options
Show help text for a selected cell: If you check this box, when an invalid input is done in the cell with a drop-down list, a pop-up message window appears with the text entered
If the data is invalid
Show a warning: You will receive an alert when you enter an invalid value in the cell but you can still enter the value.
Reject the input: You can’t input an invalid value in the cell.
Display style (Chip / Arrow / Plain text): This defines the shape and design of a drop-down list in a cell.
Once you have created a drop-down list, a drop-down arrow will appear in the cell. You can click the arrow to display the list of items and select the item you want.
How to change or delete a pull-down list in Google Sheets
You can edit an existing drop-down menu by following the steps below.
- Put your cursor on a pull-down menu.
- Click the pencil icon on the bottom right of the small pop-up.
- Go to “Data validation rule” on the right side, click “Remove rule” at the bottom or make adjustments.
A way to delete all data validation rules is as follows:
- Navigate to the “Data” tab in the menu bar
- Go to “Data validation”
- Click “Remove all” - Note this action removes not only all drop-down lists but also other data validation rules
- Thus, if you want to delete the rules individually, hover your cursor on the right side of each item
What is a dependent drop-down list in Google Sheets?
A dependent drop-down list in Google Sheets is a drop-down list that is dependent on the selection made in another drop-down list. For example, you might have a list of countries in one drop-down list, and a list of cities in another.
When you select a country from the first drop-down list, the options in the second drop-down list will change to show the cities that are located in that country.
How to make a dependent drop-down list in Google Sheets
To create a dependent drop-down list in Google Sheets, you will need to use a combination of the Data Validation and INDEX-MATCH functions or other lookup formulas. Here is a step-by-step guide: Dependent Drop-down List in Google Sheets: Explained
