A drop-down list in Google Sheets can be helpful when you want the user to select from pre-defined options. Its a great way to make the data entry consistent.
Watch Video Creating Drop Down Lists in Google Sheets
For example, a user from a drop-down list can select easily the country as the US. But if you give him the option to enter it manually, he may enter US or USA, or United States, or the United States of America.
You see the benefit here. By giving him a ready option in the form of a drop down, you have made data entry easy as well as consistent.
In this tutorial, youll learn:
- How to Create a Drop Down List in Google Sheets.
- How to Copy Drop Down Lists.
- How to Remove Drop Down Lists from Cells.
There are two ways to create a drop down list in Google Sheets:
- Using a range of cells to create the drop down.
- Manually entering the items of the drop down list.
Using a Range of Cells to Create the Drop Down List in Google Sheets
Suppose you have a country list in cells A1:A6, and you want to get drop down list in cells B1:B4.
Here are the steps to do this:
- Select the cell in which you want to create the Drop Down list. If you want to get the drop down list in multiple cells, select all the cells.
- Go to Data > Validation.
- In the Data Validation pop-up box, make the following changes:
- Cell Range:Sheet1!B1:B4
- Criteria: List from a range [Sheet1!A1:A6]
- On Invalid data:Show warning
- Appearance: Check the Display in-cell button to show list option
- Click on Save.
This will create a drop-down list in cells B1:B4 and will show the items in the range A1:A6.
In the cells where you have the drop down list, you can either select an option from the drop down, or you can enter it manually. If you enteranything which does not match the items specified, you will see the cell get highlighted [with a red triangle at the top right of the cell] as shown below:
There is also an optionto disable data entry in the cells if the entered data does not match the items specified. To do this, in the data validation settings [as shown above], in the On invalid data, select Reject input option.
Now if you enter anything that does not match the specified items, you will see an Invalid Note as shown below:
Manually Entering the Items of the Drop Down List
If you want to create a drop down list with static options [such as Yes/No, High/Medium/Low, Agree/Disagree], then you can also manually enter these options in data validation.
Here are the steps:
- Select the cell in which you want to create the Drop Down list. If you want to get the drop down list in multiple cells, select all the cells.
- Go to Data > Validation.
- In the Data Validation pop-up box, make the following changes:
- Cell Range:Sheet1!B1:B4
- Criteria: List of items[in the field to the right, manually enter the items separated by comma]
- On Invalid data: Show warning
- Appearance: Check the Display in-cell button to show list option
- Click on Save.
This will create a drop down list in the selected cells will show thespecified items.
How to Copy Drop Down Lists in Google Sheets
If you have a drop down list in one [or more cells] and you want to quickly copy the drop down, you can do so by using this simple copy paste trick.
Here are the steps:
- Copy the cell which has the drop down list that you want to copy.
- Select the cell/cells in which you want to copy the drop down list.
- Right-click and go to Paste Special > Paste data validation only.
This will copy the drop down list to the selected cells.
Note: You can do the same using a simple copy and paste as well, but that would copy the formatting as well.
How to Remove Drop Down Lists from Cells
To remove a drop down list from cells in Google Sheets:
- Select cells from which you want to delete the drop down list.
- Go to Data > Validation.
- Click on the Remove validation button.
You May Also Like the Following Google Sheets Tutorials: