How to edit a drop-down list in Google Sheets

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

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

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

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

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).