If youâre working with others on a shared Google Sheets file, sometimes people can enter unexpected data or something that breaks a formula. One way to ensure everyone enters the data you want is to provide it for them in a drop-down validation list.
How to Create a Drop-Down List
A drop-down list is a great way to ensure the data people enter into your form, application, or spreadsheet is exactly what youâre expecting. It also provides a much faster way for people to input that data since theyâre selecting from a pre-configured list you provide.
The first thing you need to do is open up your Google Sheets file and select the cell(s) for which you want to use a drop-down list.
Next, open the âDataâ menu and select the âData Validationâ command.
From the Criteria drop-down, choose either âList From a Rangeâ or âList of Items.â
- List From a Range:Â A list of values that have been selected from other cells in the same or a different sheet. For example, if you want to use the values in cells B1-B9 on sheet 2, youâd type
Sheet2!B1:B9
to have the data contained in them appear in the drop-down list, or by selecting any of the cells from your Sheet directly. - List of Items: A list of predetermined data items. This can be either text or numbers, and youâll type each value yourself, separating them by commas (and no spaces). This option does not allow you to insert data directly from other cells.
Here, weâre using the âList of Itemsâ option and providing several numeric choices.
After youâve entered the data you want to appear in a drop-down list, make sure you have âShow Drop-Down List In Cellâ option enabled or else the values wonât appear in the selected cells.
You can also select what happens when someone tries to enter a value that isnât on the list. The âShow Warningâ option lets them enter the invalid data, but marks it in the sheet (weâll look at how in just a bit). The âReject Inputâ option prevents them from entering anything thatâs not on your list.
And finally, you can enable the âShow validation help textâ option to give people some indication about what they can choose in the cells. After selecting the option, type whatever instructions you want.
Go ahead and click âSaveâ when youâre done.
How to Use Your New Drop-Down List
When youâre done, anyone using the sheet can click the drop-down arrow in those cells and choose a value from the list.
If you selected the âShow validation help textâ option, that text pops up whenever anyone selects one of the validated cells.
If someone enters a value that does not match something on the list and you have the âShow Warningâ option turned on, the invalid data is marked in the cell.
Hovering your mouse over it shows why itâs marked.
If instead, you have the âReject Inputâ option selected, people will get a warning like this one when they try to enter anything that isnât on your list.
If you need to remove or modify any of the items from your drop-down list head back to Data> Data Validation to edit any items from the lists youâve created. Removing the list completely is as easy as clicking the âRemove Validationâ button located at the bottom.