A drop down list in Google Sheets can be helpful when you want the user to select from pre-defined options. It’s a great way to make the data entry consistent.
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, you’ll learn:
There are two ways to create a 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:
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 enter anything 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 option to 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:
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:
This will create a drop down list in the selected cells will show the specified items.
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:
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.
To remove a drop down list from cells in Google Sheets:
Related: Creating a drop down list in Excel.
You May Also Like the Following Google Sheets Tutorials:
How to Use OR Function in Google Sheets (with Examples)
How to Use IMPORTRANGE Function in Google Sheets (with Examples)
How to Use IMPORTDATA function in Google Sheets
Calculate the Number of Days Between Two Dates in Google Sheets
Using IFS Function in Google Sheets to Test Multiple Conditions