Drop down lists in Google Sheets are a terrific way to make your data entering process more dynamic and interesting. Drop down menus make it easy to add commonly used choices to spreadsheets that are being updated. It is not needed to utilize two spreadsheets to construct a drop down list, but it has shown to be more successful in the long run for handling drop down lists in bigger size projects, which is why I recommend it. Because of the nature of this lesson, we will be utilizing two distinct spreadsheets to create our drop down list.
When you’re working with others on a Google Sheets file, it’s possible that someone will input unexpected data or anything that will cause a formula to fail. To guarantee that everyone inputs the information you want them to, you may include the information you want them to enter in a drop-down validation list.
In this tutorial, you will learn how to make drop down lists in Google Sheets.
Read Also: How to Merge Cells in Google Sheets
How to Make a Drop-Down List
When users fill out your form, application, or spreadsheet, a drop-down list is a terrific method to guarantee that the information they provide is precisely what you want. It also allows individuals to submit data much more quickly because they are picking from a pre-configured list that you have provided.
It is necessary to open your Google Sheets file and choose the cell or cells for which you wish to utilize a drop-down list as the first step.
Open the “Data” menu and pick the “Data Validation” command from the drop-down list.
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:B9to 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.
We’re utilizing the “List of Items” option in this case, and we’re supplying a number of numerical options.
After you’ve entered the information you want to see in a drop-down list, check to see if the “Show Drop-Down List In Cell” option is activated; otherwise, the values will not appear in the cells you have selected.
If someone attempts to input a value that isn’t on the list, you may specify what should happen in such case. The “Show Warning” option allows users to continue entering incorrect data, but it records it in the sheet (we’ll go through how to do this in a minute). It is possible to prohibit them from entering anything that is not on your list by selecting “Reject Input.”
Finally, you can enable the “Show validation assistance text” option to provide visitors with some guidance on what they may select from the drop-down menus. After selecting the option, you can enter in any instructions you wish to be shown.
When you’re finished, go ahead and hit the “Save” button.