A dropdown list can make data entry easier when developing Google Sheets that other people need to fill out.
You can either copy the elements for your dropdown list from another set of cells or type them in manually. Learn how to do both, as well as how to change a dropdown list that already exists.
In Google Sheets, make manual dropdown lists.
The quickest way to make a dropdown list in Google Sheets is to list the items under the data validation settings.
To do so, follow these steps:
- Choose the cell where you want the dropdown list to appear. From the menu, choose Data, and then Data Validation.
- Select List of things next to Criteria.
- Type the things you wish to include in the dropdown list in the field next to this selection, separated by commas.
- Press the Save button, and the cell you selected will now have a dropdown list.
Validation Options for Data
There are a few key parameters to keep in mind in the Data Validation box.
The dropdown arrow will not appear if Show dropdown list in cell is deselected. When the user begins typing, however, the list items will appear.
If you select Show warning in the On invalid data section, a red warning indication will appear. This prompts the user to note that the written item does not match anything in the list.
Instead, if you choose Reject input, Google Sheets will reject the entry and replace it with the first option in the dropdown list.
If you select Show validation help text in the Appearance section and enter text into the area below it, that text will appear when the user selects the dropdown cell.
Using a Google Sheets Range, create dropdown lists
Using the contents of a range of cells to fill the list in Google Sheets is a more dynamic technique to construct dropdown lists.
To do so, follow these steps:
- Begin by creating your validation lists in any cell range. It’s not necessary for these to be in the same spreadsheet. These lists can also be created and selected in other spreadsheet tabs.
- Next, choose the cell where you want the dropdown list to appear. From the menu, choose Data, and then Data Validation.
- From the Criteria dropdown list, choose List from a range this time. Then, to open the range selection window, select the small grid selection icon.
- In the Select a data range section, type the range you want to use as your list, and the range text will appear.
- To return to the validation window, select OK to exit the selection window. To finish, configure the remaining dropdown options and then click the Save button.
- The range data will now appear as dropdown list items in the cell you selected.
- Repeat steps 6–7 for any other columns you want to use as a dynamic dropdown list.
Using ranges as your data source allows you to maintain your spreadsheets up to date without having to manually update each dropdown list you’ve established.
Dropdown Lists in Google Sheets: Interesting Facts
Dropdown lists attached to ranges in Google Sheets are the most beneficial since they drastically minimize spreadsheet upkeep.
With a single range change, you can update several cells.
This is especially true if you have a lot of cells that all pull data from the same set of objects. You’ll only need to edit or update a single range if you wish to update or change those list items.
Even if there are hundreds of cells with those list items, adjusting the range just once will automatically update all of those dropdown cells.
To Save Time, Copy Validated Cells
Additionally, you can save time by copying verified dropdown cells to any other cells. This saves time by eliminating the need to go through the confirmation screens again.
For selection lists such as days, months, time, and other typical datasets, copying verified cells is extremely beneficial.
Remove Cell Validation as soon as possible.
Let’s say you don’t want the list items to appear in any of the cells. By right-clicking the cell, selecting Validation, and then selecting Remove validation in the Data validation box, you may swiftly remove them.
The dropdown arrow, as well as all of the dropdown elements, will vanish from the cell. The cell will revert to being a standard spreadsheet cell.
Using Google Sheets’ Double Dropdown Lists
Passing data between sheets is another helpful technique to use Google Sheets dropdown lists. This method can also be used to communicate between people.
For example, if you have an original sheet with a list of activities performed by one team, you can use that list to create a second spreadsheet.
You might or might not wish to build that first list using the same dynamic dropdown lists discussed in the previous section.
In either case, open the validation window as explained in the previous section and pick the cell on the sheet where you wish to send the completed tasks as another dropdown list. Switch to this source task spreadsheet and pick the complete column of tasks once you’ve selected the data range (including blank cells).
The data from the tasks column has now been pulled into the destination spreadsheet. This means your second team can accomplish their own project tasks based on the previous team’s completed tasks.
The first team can keep updating the original source spreadsheet with freshly completed tasks.
The new unique jobs will display in the second team’s dropdown list because you included the blank cells in the source range.
Keep in mind that the second dropdown list will only show unique assignments. It’s not the ideal way to convey individual row items from the source, but it’s a terrific way for a second team to see all the unique stuff that other people have added to a spreadsheet.
How Will You Use Dropdown Lists in Google Sheets?
As you can see, Google Sheets provides a variety of options for pulling data from other fields to populate dropdown lists. Alternatively, if your data input requirements are straightforward, you can manually insert dropdown list items separated by commas.
In any case, you should be able to make data entry as simple as possible for any of your spreadsheets.