how to add drop down list in google sheets

Learn how to change the drop-down options in Google Sheets, edit list items manually, or change the contents of a range of cells.

Two images: (left) Drop-down list of items A,B,C,D. Hand-drawn arrow from the list to (right) image with Data validation details, arrow pointing to the manually entered list item details. — Image: Andy Wolber/TechRepublic

A drop-down list helps speed entry and standardize data in a Google Sheet. Select a cell, choose an item from the list, and continue on to your next task. Whether you use Google Sheets in a desktop-class web browser or in the Google Sheets mobile app on Android or iOS, you get the benefit of fast and accurate data entry. To learn how to add data validation to one or more cells, see: How to create a drop-down list in Google Sheets.

Sometimes drop-down list options need to change. For example, you might need to add a new product, service, or option, or you may need to remove an option that is no longer needed. Regardless of what you use a drop-down list for--people, places, or things--you may want to modify the offered options at various points in time.

Since Google Sheets includes two ways to make a drop-down list, the method you use to edit the drop-down options will vary. The following steps cover how to edit a manually entered list of data, as well as how to modify a list created from the contents of a range of cells. In either case, you will first need to open the Google Sheet, and you'll also need to have edit access to the sheet and cells you wish to change.

Note: You may edit a drop-down list either in Google Sheets in a desktop-class web browser or in the Google Sheets app on Android.

SEE: Google Sheets: Tips every user should master (TechRepublic)

How to edit a manually entered drop-down list in Google Sheets

1. Click or tap to select the cell or cells with the data validation setting you wish to change.
2. In a web browser, choose Data | Data Validation. In the Google Sheets Android app, tap the three vertically-arranged dots menu in the upper-right, then tap Data Validation.
3. In a web browser, edit the List Of Items in the text box to the right of the Criteria field (Figure A). Edit the text to add or remove items as desired, and be sure to separate each item by a comma.

Figure A

Screenshot of Data validation options, with Drop-down items (to the right of Criteria field) separated by commas. — In a web browser, select a cell or cells, choose Data | Data Validation, then edit the text in the List Of Items field. Select Save to apply your changes.

In the Google Sheets Android app, tap +Add, input text to add an item, and tap the checkmark to the right of the field when done (Figure B). Repeat the process for each item you want to add. To remove an item, tap on a displayed item, then tap the delete key.

Figure B

Screenshots that show the sequence: select cells, select the three-dot menu, then Data validation, then either edit the list options or adjust the range. — In the Google Sheets Android app, select a cell or cells, then tap the three vertically arranged dots (left), select Data Validation (middle), and then either edit list items (right-top) or adjust the list range (right-bottom).

4. When finished with the changes, click or tap Save to apply your changes to the selected cells.
5. Click or tap on one of the modified cells to view the edited drop-down list, then verify that the edited content displays as you expect.

SEE: G Suite: Tips and tricks for business professionals (TechRepublic download)

How to edit content in a drop-down list from a range in Google Sheets

1. Go to the section of your Google Sheet with the cells that contain the specified range for data validation.
2. Edit the contents of these cells as necessary to modify the drop-down items displayed (Figure C). After you edit each cell, the drop-down data validation list will automatically display the new data when selected.

Figure C

Screenshot of cell that displays four drop-down items, with a range of cells shown to the right that each contain the source data (i.e., cell I26: Item A, I27: Item B, I28: Item C, I29: Item D, shown in cell C26 as 4 different drop-down options). — If your data validation draws content from cells in range, edit the content of the cells in that range to change the options. (This will not change the contents of any previously entered data; it will only change the options displayed for any future edits or entries.)

3. After you edit the cells, click or tap to view the drop-down list, and verify that the content displays correctly.

How to adjust the range in Google Sheets

If the number of options you need to display has changed, you will need to modify the range of cells specified. For example, if the number of items is fewer, decrease the range to avoid showing blank options; or, if the number of items needs to increase, expand the range specified to ensure that all options display.

1. Click (or tap) to select the cell (or cells) with the data validation setting you wish to change.
2. In a web browser, choose Data | Data Validation. In the Google Sheets Android app, tap the three vertically-arranged dots menu in the upper-right, then tap Data Validation.
3. In the Range field, modify the range to indicate the sheet and cells that contain your desired drop-down list options (Figure D). For example, if the original range was "Sheet1!I26:I29" you might modify that to "Sheet1!I26:I28" to reduce the range by one cell, or to "Sheet1!I26:I30" to increase the range by one cell.

Figure D

Google Sheets on desktop with Data > Data validation screen displayed, with range of Sheet1!I26:I29 as the current range. — For data validation fields that rely on content from cells in a range, you may need to adjust the range when you change the number of options. For example, to add an option, you would extend the range.

4. Click or tap Save to preserve your adjusted range. As always, click or tap in the modified cell to check that the change displays as desired.

What's your experience?

Which method do you use most often to validate data in Google Sheet: A manually entered list or a list created from the contents of a range of cells? What type of information do you typically gather in cells where you use drop-down options? Let me know, either in the comments below or on Twitter (@awolber).

Google Weekly Newsletter

Learn how to get the most out of Google Docs, Google Cloud Platform, Google Apps, Chrome OS, and all the other Google products used in business environments. Delivered Fridays

Also see

10 free alternatives to Microsoft Word and Excel (TechRepublic download)
Choosing your Windows 7 exit strategy: Four options (TechRepublic Premium)
How to add a drop-down list to an Excel cell (TechRepublic)
How to collect data with Google Forms: 4 steps (TechRepublic)
Microsoft Office 365 for business: Everything you need to know (ZDNet)
It takes work to keep your data private online. These apps can help (CNET)
How-To Tips: More easy-to-follow tutorials (TechRepublic on Flipboard)